Assign a value to an SQL variable
PL/pgPSM supports three forms of assignment statement:
- singleton variable assignment
- multiple variable assignment (compatible with standard SQL/PSM)
- multiple variable assignment (compatible with DB2 and MySQL)
Multiple variable assignement like DB2 is evaluated repeatable from left to right and it's equeal to a sequence of singleton variable assignment. In standard multiple variable assignment is expression list evaluated first and result is assigned to target variable list. It's allways faster than multiple variable assignment like DB2 and it is preferred.
CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$ BEGIN DECLARE a, b, c int; -- singleton variable assignment SET a = 0; SET b = a + 1; SET c = b + 1; -- multiple variable assignment (DB2 and MySQL style) SET a = 0, b = a + 1, c = b + 1; -- multiple variable assignment (ANSI) SET (a,b,c) = (0, 0 + 1, 0 + 2); -- assign subquery SET a = (SELECT f.a FROM Foo f); SET (a, b, c) = (SELECT f.a, f.b, f.c FROM Foo f); END; $$ LANGUAGE plpgpsm;
SET '(' variable [, variable [, ...]] ')' = '(' expression ')' ';' SET variable '=' expression [, variable '=' expression [, ...]] ';'