Assign select value to variable in PostgreSQL 9.3

14,638

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

Share:
14,638
MAK
Author by

MAK

Database Developer (Microsoft SQL Server and PostgreSQL).

Updated on June 06, 2022

Comments

  • MAK
    MAK almost 2 years

    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;