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

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

GMB · Accepted Answer · 2019-12-26 18:01:05Z

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, 2019 at 18:01

answered Dec 26, 2019 at 17:43

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Colby Willoughby Over a year ago

Hmmm, although this looks right, it is now returning an exception that reads "SQL Error [42601]: ERROR: syntax error at or near "procedure""

GMB Over a year ago

@ColbyWilloughby: you can see that it is compiling in the DB Fiddle... Which version of Postgres are you running?

Colby Willoughby Over a year ago

Looks like PostgreSQL 10.9

GMB Over a year ago

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

Colby Willoughby Over a year ago

Was JUST about to say the same thing! Looks like that works! Thanks for your help!

Collectives™ on Stack Overflow

Syntax error when declaring a postgres variable [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related