Assign select value to variable in PostgreSQL 9.3

Question

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;

score 13 · Accepted Answer · 2015-01-08 13:42:32Z

13

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

edited Jan 8, 2015 at 13:42

answered Jan 8, 2015 at 13:35

user330315

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

2 Comments

MAK Over a year ago

What if I want to assign multiple values to multiple variables?

user330315 Over a year ago

As stated in the manual: "where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields" See my edit

Collectives™ on Stack Overflow

Assign select value to variable in PostgreSQL 9.3

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related