src/expected/plpgsql_call.out

--
-- Tests for procedures / CALL syntax
--
CREATE PROCEDURE test_proc1()
LANGUAGE plpgsql
AS $$
BEGIN
    NULL;
END;
$$;
CALL test_proc1();
-- error: can't return non-NULL
CREATE PROCEDURE test_proc2()
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN 5;
END;
$$;
ERROR:  RETURN cannot have a parameter in a procedure
LINE 5:     RETURN 5;
                   ^
CREATE TABLE test1 (a int);
CREATE PROCEDURE test_proc3(x int)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 VALUES (x);
END;
$$;
CALL test_proc3(55);
SELECT * FROM test1;
 a
----
 55
(1 row)

-- nested CALL
TRUNCATE TABLE test1;
CREATE PROCEDURE test_proc4(y int)
LANGUAGE plpgsql
AS $$
BEGIN
    CALL test_proc3(y);
    CALL test_proc3($1);
END;
$$;
CALL test_proc4(66);
SELECT * FROM test1;
 a
----
 66
 66
(2 rows)

CALL test_proc4(66);
SELECT * FROM test1;
 a
----
 66
 66
 66
 66
(4 rows)

-- output arguments
CREATE PROCEDURE test_proc5(INOUT a text)
LANGUAGE plpgsql
AS $$
BEGIN
    a := a || '+' || a;
END;
$$;
CALL test_proc5('abc');
    a
---------
 abc+abc
(1 row)

CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)
LANGUAGE plpgsql
AS $$
BEGIN
    b := b * a;
    c := c * a;
END;
$$;
CALL test_proc6(2, 3, 4);
 b | c
---+---
 6 | 8
(1 row)

DO
LANGUAGE plpgsql
$$
DECLARE
    x int := 3;
    y int := 4;
BEGIN
    CALL test_proc6(2, x, y);
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;
INFO:  x = 6, y = 8
DO
LANGUAGE plpgsql
$$
DECLARE
    x int := 3;
    y int := 4;
BEGIN
    CALL test_proc6(2, x + 1, y);  -- error
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;
ERROR:  procedure parameter "b" is an output parameter but corresponding argument is not writable
CONTEXT:  PL/pgSQL function inline_code_block line 6 at CALL
DO
LANGUAGE plpgsql
$$
DECLARE
    x int := 3;
    y int := 4;
BEGIN
    FOR i IN 1..5 LOOP
        CALL test_proc6(i, x, y);
        RAISE INFO 'x = %, y = %', x, y;
    END LOOP;
END;
$$;
INFO:  x = 3, y = 4
INFO:  x = 6, y = 8
INFO:  x = 18, y = 24
INFO:  x = 72, y = 96
INFO:  x = 360, y = 480
-- recursive with output arguments
CREATE PROCEDURE test_proc7(x int, INOUT a int, INOUT b numeric)
LANGUAGE plpgsql
AS $$
BEGIN
IF x > 1 THEN
    a := x / 10;
    b := x / 2;
    CALL test_proc7(b::int, a, b);
END IF;
END;
$$;
CALL test_proc7(100, -1, -1);
 a | b
---+---
 0 | 1
(1 row)

-- inner COMMIT with output arguments
CREATE PROCEDURE test_proc7c(x int, INOUT a int, INOUT b numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  a := x / 10;
  b := x / 2;
  COMMIT;
END;
$$;
CREATE PROCEDURE test_proc7cc(_x int)
LANGUAGE plpgsql
AS $$
DECLARE _a int; _b numeric;
BEGIN
  CALL test_proc7c(_x, _a, _b);
  RAISE NOTICE '_x: %,_a: %, _b: %', _x, _a, _b;
END
$$;
CALL test_proc7cc(10);
NOTICE:  _x: 10,_a: 1, _b: 5
-- named parameters and defaults
CREATE PROCEDURE test_proc8a(INOUT a int, INOUT b int)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'a: %, b: %', a, b;
  a := a * 10;
  b := b + 10;
END;
$$;
CALL test_proc8a(10, 20);
NOTICE:  a: 10, b: 20
  a  | b
-----+----
 100 | 30
(1 row)

CALL test_proc8a(b => 20, a => 10);
NOTICE:  a: 10, b: 20
  a  | b
-----+----
 100 | 30
(1 row)

DO $$
DECLARE _a int; _b int;
BEGIN
  _a := 10; _b := 30;
  CALL test_proc8a(_a, _b);
  RAISE NOTICE '_a: %, _b: %', _a, _b;
  CALL test_proc8a(b => _b, a => _a);
  RAISE NOTICE '_a: %, _b: %', _a, _b;
END
$$;
NOTICE:  a: 10, b: 30
NOTICE:  _a: 100, _b: 40
NOTICE:  a: 100, b: 40
NOTICE:  _a: 1000, _b: 50
CREATE PROCEDURE test_proc8b(INOUT a int, INOUT b int, INOUT c int)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
  a := a * 10;
  b := b + 10;
  c := c * -10;
END;
$$;
DO $$
DECLARE _a int; _b int; _c int;
BEGIN
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8b(_a, _b, _c);
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
  CALL test_proc8b(_a, c => _c, b => _b);
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
END
$$;
NOTICE:  a: 10, b: 30, c: 50
NOTICE:  _a: 100, _b: 40, _c: -500
NOTICE:  a: 100, b: 40, c: -500
NOTICE:  _a: 1000, _b: 50, _c: 5000
CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int, INOUT c int DEFAULT 11)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
  a := a * 10;
  b := b + 10;
  c := c * -10;
END;
$$;
DO $$
DECLARE _a int; _b int; _c int;
BEGIN
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8c(_a, _b, _c);
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8c(_a, c => _c, b => _b);
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8c(c => _c, b => _b, a => _a);
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
END
$$;
NOTICE:  a: 10, b: 30, c: 50
NOTICE:  _a: 100, _b: 40, _c: -500
NOTICE:  a: 10, b: 30, c: 50
NOTICE:  _a: 100, _b: 40, _c: -500
NOTICE:  a: 10, b: 30, c: 50
NOTICE:  _a: 100, _b: 40, _c: -500
DO $$
DECLARE _a int; _b int; _c int;
BEGIN
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8c(_a, _b);  -- fail, no output argument for c
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
END
$$;
ERROR:  procedure parameter "c" is an output parameter but corresponding argument is not writable
CONTEXT:  PL/pgSQL function inline_code_block line 5 at CALL
DO $$
DECLARE _a int; _b int; _c int;
BEGIN
  _a := 10; _b := 30; _c := 50;
  CALL test_proc8c(_a, b => _b);  -- fail, no output argument for c
  RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
END
$$;
ERROR:  procedure parameter "c" is an output parameter but corresponding argument is not writable
CONTEXT:  PL/pgSQL function inline_code_block line 5 at CALL
-- transition variable assignment
TRUNCATE test1;
CREATE FUNCTION triggerfunc1() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    z int := 0;
BEGIN
    CALL test_proc6(2, NEW.a, NEW.a);
    RETURN NEW;
END;
$$;
CREATE TRIGGER t1 BEFORE INSERT ON test1 EXECUTE PROCEDURE triggerfunc1();
INSERT INTO test1 VALUES (1), (2), (3);
UPDATE test1 SET a = 22 WHERE a = 2;
SELECT * FROM test1 ORDER BY a;
 a
----
  1
  3
 22
(3 rows)

DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc3;
DROP PROCEDURE test_proc4;
DROP TABLE test1;
-- more checks for named-parameter handling
CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL)
AS $$
BEGIN
  v_Text := 'v_cnt = ' || v_cnt;
END
$$ LANGUAGE plpgsql;
DO $$
DECLARE
  v_Text text;
  v_cnt  integer := 42;
BEGIN
  CALL p1(v_cnt := v_cnt);  -- error, must supply something for v_Text
  RAISE NOTICE '%', v_Text;
END;
$$;
ERROR:  procedure parameter "v_text" is an output parameter but corresponding argument is not writable
CONTEXT:  PL/pgSQL function inline_code_block line 6 at CALL
DO $$
DECLARE
  v_Text text;
  v_cnt  integer := 42;
BEGIN
  CALL p1(v_cnt := v_cnt, v_Text := v_Text);
  RAISE NOTICE '%', v_Text;
END;
$$;
NOTICE:  v_cnt = 42
DO $$
DECLARE
  v_Text text;
BEGIN
  CALL p1(10, v_Text := v_Text);
  RAISE NOTICE '%', v_Text;
END;
$$;
NOTICE:  v_cnt = 10
DO $$
DECLARE
  v_Text text;
  v_cnt  integer;
BEGIN
  CALL p1(v_Text := v_Text, v_cnt := v_cnt);
  RAISE NOTICE '%', v_Text;
END;
$$;
NOTICE:  <NULL>