Postgresql assign a select query to variable in the function

Question

I am using Postgresql 9.3 and wrote a function as below:

    create or replace function test(building text,floor text) returns void as $$
    Declare
    id integer;
    num integer := 0;
    Begin

    num=num+100

    id :=select to_number(
          (select 
              (select code from buildings where name=building) || floor 
              || (select num::text)),'99999999'
    );

    update table set col1=id;

    End;
    $$
    language plpgsql;

What I expect is that my id variable will be assigned a number value example: 12502100 from the select to_number(...) query.

However I got the below error

ERROR:  syntax error at or near ":="
LINE 10: source :=(select code from buildings where name='I3')

How can I assign the query result (with some string operations) into the variable id?

I am also failed with Select Into id... method.

Pavel Stehule · Accepted Answer · 2013-10-10 07:34:34Z

6

You don't need use SELECT for function evaluation.

id := to_number((SELECT code FROM buildings WHERE name = building) 
                                                      || floor || num::text,
                '999999999');

other possibility (and usually better) is using function in expression list (result field list)

id := (SELECT to_number(code || floor || num::text, '99999999') 
          FROM buildings WHERE name = building)

Use SELECT only when you need a query to data, not for function or variable evaluation!

edited Oct 10, 2013 at 7:34

answered Oct 9, 2013 at 9:05

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

4 Comments

Xianlin Over a year ago

your 2nd method gives me error " mismatched parentheses at or near ")"", the 1st method is working.

Pavel Stehule Over a year ago

@Xianlin: sorry, fixed there was one parentheses more

thames Over a year ago

how do you do it with multiple variables and one statment? i.e. you want to fill var1 and var2 with (SELECT field1, field2 FROM buildings WHERE name = building); var1 should contain field1 result and var2 should have field2 result.

Pavel Stehule Over a year ago

@thames: You have to use SELECT INTO statement for multiple variables

Collectives™ on Stack Overflow

Postgresql assign a select query to variable in the function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related