Search code examples
postgresqlpostgresql-9.3

Assign select value to variable in PostgreSQL 9.3


I have the following table:

Example:

create table test
( 
 id int,
 name varchar(10),
 city varchar(10)
);

I want to assign ID value from table to variable in the function.

Function:

create or replace function testing(ids int,names varchar(10),citys varchar(10)
returns void as
$body$
Declare
       ident int;
BEGIN
       ident := SELECT ID FROM test;
       raise info '%',ident;
END;
$body$
Language plpgsql;

Error Details:

ERROR:  syntax error at or near "SELECT"
LINE 12:  ident := SELECT ID from test;

Solution

  • Use select ... into

    create or replace function testing(ids int,names varchar(10),citys varchar(10)
    returns void as
    $body$
    Declare
           ident int;
           foo   text;
    BEGIN
           SELECT ID, some_col  
              into ident, foo 
           FROM test;
           raise info '%',ident;
    END;
    $body$
    Language plpgsql;
    

    More details and examples are in the manual:
    http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW