Procedure calling and value returning

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

Syntax

  CALL '(' coma separated list of arguments ')'

Syntax

  RETURN [NEXT] [expression]
CREATE OR REPLACE FUNCTION tab(OUT a1 int, OUT a2 int, OUT a3 int, OUT a4 int)
RETURNS SETOF RECORD AS
$$
  BEGIN
    DECLARE i int DEFAULT 0;
    SET a4 = 0;
    WHILE i < 4 DO
      SET a1 = a4 + 1, a2 = a1 + 1, a3 = a2 + 1, a4 = a3 + 1;
      SET i = i + 1;
      RETURN NEXT;
    END WHILE;
    RETURN;
  END;
$$ LANGUAGE plpgpsm;
CREATE OR REPLACE FUNCTION tab(p int)
RETURNS SETOF Foo AS
$$
  BEGIN
    -- any calculation
    IF p < 0 THEN SET p = 0; END IF;
    IF p > 20 THEN SET p = 20; END IF;
    -- using table expression
    RETURN 
      SELECT *
         FROM Foo
        WHERE Foo.a < tab.p;
  END;
$$ LANGUAGE plpgpsm;