Assignment statement

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

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;

Syntax

SET '(' variable [, variable [, ...]] ')' = '(' expression ')' ';'
SET variable '=' expression [, variable '=' expression [, ...]] ';'