CREATE PROCEDURE test_proc1()

LANGUAGE plpgsql

AS $$

NULL;

END;

$$;

CALL test_proc1();

CREATE PROCEDURE test_proc2()

LANGUAGE plpgsql

AS $$

RETURN 5;

END;

$$;

CREATE TABLE test1 (a int);

CREATE PROCEDURE test_proc3(x int)

LANGUAGE plpgsql

AS $$

INSERT INTO test1 VALUES (x);

END;

$$;

CALL test_proc3(55);

SELECT * FROM test1;

TRUNCATE TABLE test1;

CREATE PROCEDURE test_proc4(y int)

LANGUAGE plpgsql

AS $$

CALL test_proc3(y);

CALL test_proc3($1);

END;

$$;

CALL test_proc4(66);

SELECT * FROM test1;

CALL test_proc4(66);

SELECT * FROM test1;

CREATE PROCEDURE test_proc5(INOUT a text)

LANGUAGE plpgsql

AS $$

a := a || '+' || a;

END;

$$;

CALL test_proc5('abc');

CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)

LANGUAGE plpgsql

AS $$

b := b * a;

c := c * a;

END;

$$;

CALL test_proc6(2, 3, 4);

DO

LANGUAGE plpgsql

$$

DECLARE

x int := 3;

y int := 4;

CALL test_proc6(2, x, y);

RAISE INFO 'x = %, y = %', x, y;

END;

$$;

DO

LANGUAGE plpgsql

$$

DECLARE

x int := 3;

y int := 4;

CALL test_proc6(2, x + 1, y); -- error

RAISE INFO 'x = %, y = %', x, y;

END;

$$;

DO

LANGUAGE plpgsql

$$

DECLARE

x int := 3;

y int := 4;

FOR i IN 1..5 LOOP

CALL test_proc6(i, x, y);

RAISE INFO 'x = %, y = %', x, y;

END LOOP;

END;

$$;

CREATE PROCEDURE test_proc7(x int, INOUT a int, INOUT b numeric)

LANGUAGE plpgsql

AS $$

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);

CREATE PROCEDURE test_proc7c(x int, INOUT a int, INOUT b numeric)

LANGUAGE plpgsql

AS $$

a := x / 10;

b := x / 2;

COMMIT;

END;

$$;

CREATE PROCEDURE test_proc7cc(_x int)

LANGUAGE plpgsql

AS $$

DECLARE _a int; _b numeric;

CALL test_proc7c(_x, _a, _b);

RAISE NOTICE '_x: %,_a: %, _b: %', _x, _a, _b;

END

$$;

CALL test_proc7cc(10);

CREATE PROCEDURE test_proc8a(INOUT a int, INOUT b int)

LANGUAGE plpgsql

AS $$

RAISE NOTICE 'a: %, b: %', a, b;

a := a * 10;

b := b + 10;

END;

$$;

CALL test_proc8a(10, 20);

CALL test_proc8a(b => 20, a => 10);

DO $$

DECLARE _a int; _b int;

_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

$$;

CREATE PROCEDURE test_proc8b(INOUT a int, INOUT b int, INOUT c int)

LANGUAGE plpgsql

AS $$

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;

_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

$$;

CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int, INOUT c int DEFAULT 11)

LANGUAGE plpgsql

AS $$

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;

_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

$$;

DO $$

DECLARE _a int; _b int; _c int;

_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

$$;

DO $$

DECLARE _a int; _b int; _c int;

_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

$$;

CREATE PROCEDURE test_proc9(IN a int, OUT b int)

LANGUAGE plpgsql

AS $$

RAISE NOTICE 'a: %, b: %', a, b;

b := a * 2;

END;

$$;

DO $$

DECLARE _a int; _b int;

_a := 10; _b := 30;

CALL test_proc9(_a, _b);

RAISE NOTICE '_a: %, _b: %', _a, _b;

END

$$;

TRUNCATE test1;

CREATE FUNCTION triggerfunc1() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

z int := 0;

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;

DROP PROCEDURE test_proc1;

DROP PROCEDURE test_proc3;

DROP PROCEDURE test_proc4;

DROP TABLE test1;

CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL)

AS $$

v_Text := 'v_cnt = ' || v_cnt;

END

$$ LANGUAGE plpgsql;

DO $$

DECLARE

v_Text text;

v_cnt integer := 42;

CALL p1(v_cnt := v_cnt); -- error, must supply something for v_Text

RAISE NOTICE '%', v_Text;

END;

$$;

DO $$

DECLARE

v_Text text;

v_cnt integer := 42;

CALL p1(v_cnt := v_cnt, v_Text := v_Text);

RAISE NOTICE '%', v_Text;

END;

$$;

DO $$

DECLARE

v_Text text;

CALL p1(10, v_Text := v_Text);

RAISE NOTICE '%', v_Text;

END;

$$;

DO $$

DECLARE

v_Text text;

v_cnt integer;

CALL p1(v_Text := v_Text, v_cnt := v_cnt);

RAISE NOTICE '%', v_Text;

END;

$$;