Syntax error when declaring a postgres variable [duplicate]

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".

Answer 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'