Why am I getting an error declaring / assigning variables in Postgres?

Question

I want to declare and assign variables on a Postgres 12.10 instance.

According to the Postgres documentation I should be able to declare and assign variables as follows

DECLARE
  x integer := 1;
  y integer := x + 1;

but I get the error

ERROR:  syntax error at or near "integer"
LINE 2:   x integer := 1;
            ^
SQL state: 42601
Character: 13

Can anyone see what I'm doing wrong?

Then that's no surprise. That segment of code alone doesn't make any sense and is not syntactically correct. — Laurenz Albe, Dec 07 '22 at 11:53
That is just a sample from a part of a function. Try using that in a full function code. — Cetin Basoz, Dec 07 '22 at 11:55
SQL has no variables, only PL/pgSQL (or other procedural languages) — , Dec 07 '22 at 11:59

Raya · Answer 1 · 2022-12-07T11:53:55.500

1

Try this syntax.

DO
$$
DECLARE
  x integer := 1;
  y integer := x + 1;
$$

edited Dec 07 '22 at 11:53

answered Dec 07 '22 at 11:50

Raya

11
1
6

I'm getting the same error – Vinyl Warmth Dec 07 '22 at 11:51
Updated my answer, try that. – Raya Dec 07 '22 at 11:54
1

Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 – Dec 07 '22 at 11:59
I didn't. I showed that it didn't throw his error. The code is above it. – Raya Dec 07 '22 at 12:00
@Raya still, an image consumes much more storage than text :) Welcome to SO. – Jim Jones Dec 07 '22 at 12:19

score 1 · Accepted Answer · answered Dec 07 '22 at 11:57

1

That code from documentation was only a sample showing you that you could use the value of a former declared value in a latter variable assignment and not meant to be run alone. You should use that in a function. ie: Check this DBFiddle demo

answered Dec 07 '22 at 11:57

Cetin Basoz

22,495
3
31
39

Why am I getting an error declaring / assigning variables in Postgres?

2 Answers2