Syntax error when declaring a postgres variable

Question

I'm getting a SQL error when trying to declare a variable in Postgresql. Check out my code below:

declare number int8;

begin
 select ID into number from public.People p where p."Name" = 'Jeff';
 if found then
   insert into public.Friends ("PeopleID", "Name", "PhoneNumber")
   values (number, 'Jeff', '205-123-4567')
 end if;
end;

When I try and run this, it returns this exception:

SQL Error [42601]: ERROR: syntax error at or near "int8"

I'm expecting there to be only one entry with the name "Jeff", so I won't have to worry about having more than one possible value for "number".

Hey Colby, these commands are not SQL. You might wanna put this code inside a plpgsql function or procedure :) — Jim Jones, Dec 26 '19 at 17:44

GMB · Accepted Answer · 2019-12-26T18:01:05.193

1

You cannot declare a variable in pure SQL in Postgres, unlike other RDBMS such as SQL Server or MySQL.

Your code compiles properly in this Postgres 12 db fiddle when I put it within plgpsql procedure (still, you should consider another name that number for a variable).

create procedure test_proc() language plpgsql AS '
declare number int8;
begin
    select ID into number from people p where p."Name" = ''Jeff'';
    if found then
        insert into friends ("PeopleID", "Name", "PhoneNumber")
            values (number, ''Jeff'', ''205-123-4567'');
    end if;
end;
';

Or in Postgres < 11 with a void function:

create function test_proc() returns void language plpgsql AS '
declare number int8;
begin
    select ID into number from people p where p."Name" = ''Jeff'';
    if found then
        insert into friends ("PeopleID", "Name", "PhoneNumber")
            values (number, ''Jeff'', ''205-123-4567'');
    end if;
end;
';

You might also want consider this insert ... select query, that achieves the same purpose in a single database call:

insert into friends("PeopleID", "Name", "PhoneNumber")
select ID, "Name", '205-123-4567' from people where p."Name" = 'Jeff'

edited Dec 26 '19 at 18:01

answered Dec 26 '19 at 17:43

GMB

216,147
25
84
135

Hmmm, although this looks right, it is now returning an exception that reads "SQL Error [42601]: ERROR: syntax error at or near "procedure"" – Colby Willoughby Dec 26 '19 at 17:53
@ColbyWilloughby: you can see that it is compiling in the DB Fiddle... Which version of Postgres are you running? – GMB Dec 26 '19 at 17:54
Looks like PostgreSQL 10.9 – Colby Willoughby Dec 26 '19 at 17:56
@ColbyWilloughby: ok Postgres 10 does not have stored procedures, so you could use a function (see my updated answer). But I would recommend to consider the query at the end of my answer. It does the same job without requiring a function/procedure. – GMB Dec 26 '19 at 18:02
Was JUST about to say the same thing! Looks like that works! Thanks for your help! – Colby Willoughby Dec 26 '19 at 18:07

Syntax error when declaring a postgres variable

1 Answers1