CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

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;

Upvotes: 4

Views: 21766

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 13

Related Questions