| CALL nonexistent(); -- error |
| ERROR: procedure nonexistent() does not exist |
| LINE 1: CALL nonexistent(); |
| ^ |
| HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. |
| CALL random(); -- error |
| ERROR: random() is not a procedure |
| LINE 1: CALL random(); |
| ^ |
| HINT: To call a function, use SELECT. |
| CREATE FUNCTION cp_testfunc1(a int) RETURNS int LANGUAGE SQL AS $$ SELECT a $$; |
| CREATE TABLE cp_test (a int, b text); |
| CREATE PROCEDURE ptest1(x text) |
| LANGUAGE SQL |
| AS $$ |
| INSERT INTO cp_test VALUES (1, x); |
| $$; |
| \df ptest1 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+--------+------------------+---------------------+------ |
| public | ptest1 | | IN x text | proc |
| (1 row) |
| |
| SELECT pg_get_functiondef('ptest1'::regproc); |
| pg_get_functiondef |
| ------------------------------------------------------ |
| CREATE OR REPLACE PROCEDURE public.ptest1(IN x text)+ |
| LANGUAGE sql + |
| AS $procedure$ + |
| INSERT INTO cp_test VALUES (1, x); + |
| $procedure$ + |
| |
| (1 row) |
| |
| -- show only normal functions |
| \dfn public.*test*1 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+--------------+------------------+---------------------+------ |
| public | cp_testfunc1 | integer | a integer | func |
| (1 row) |
| |
| -- show only procedures |
| \dfp public.*test*1 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+--------+------------------+---------------------+------ |
| public | ptest1 | | IN x text | proc |
| (1 row) |
| |
| SELECT ptest1('x'); -- error |
| ERROR: ptest1(unknown) is a procedure |
| LINE 1: SELECT ptest1('x'); |
| ^ |
| HINT: To call a procedure, use CALL. |
| CALL ptest1('a'); -- ok |
| CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg |
| CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile arg |
| SELECT * FROM cp_test ORDER BY b COLLATE "C"; |
| a | b |
| ---+------- |
| 1 | 0 |
| 1 | a |
| 1 | xyzzy |
| (3 rows) |
| |
| -- SQL-standard body |
| CREATE PROCEDURE ptest1s(x text) |
| LANGUAGE SQL |
| BEGIN ATOMIC |
| INSERT INTO cp_test VALUES (1, x); |
| END; |
| \df ptest1s |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+---------+------------------+---------------------+------ |
| public | ptest1s | | IN x text | proc |
| (1 row) |
| |
| SELECT pg_get_functiondef('ptest1s'::regproc); |
| pg_get_functiondef |
| ------------------------------------------------------- |
| CREATE OR REPLACE PROCEDURE public.ptest1s(IN x text)+ |
| LANGUAGE sql + |
| BEGIN ATOMIC + |
| INSERT INTO cp_test (a, b) + |
| VALUES (1, ptest1s.x); + |
| END + |
| |
| (1 row) |
| |
| CALL ptest1s('b'); |
| SELECT * FROM cp_test ORDER BY b COLLATE "C"; |
| a | b |
| ---+------- |
| 1 | 0 |
| 1 | a |
| 1 | b |
| 1 | xyzzy |
| (4 rows) |
| |
| -- utility functions currently not supported here |
| CREATE PROCEDURE ptestx() |
| LANGUAGE SQL |
| BEGIN ATOMIC |
| CREATE TABLE x (a int); |
| END; |
| ERROR: CREATE TABLE is not yet supported in unquoted SQL function body |
| CREATE PROCEDURE ptest2() |
| LANGUAGE SQL |
| AS $$ |
| SELECT 5; |
| $$; |
| CALL ptest2(); |
| -- nested CALL |
| TRUNCATE cp_test; |
| CREATE PROCEDURE ptest3(y text) |
| LANGUAGE SQL |
| AS $$ |
| CALL ptest1(y); |
| CALL ptest1($1); |
| $$; |
| CALL ptest3('b'); |
| SELECT * FROM cp_test; |
| a | b |
| ---+--- |
| 1 | b |
| 1 | b |
| (2 rows) |
| |
| -- output arguments |
| CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int) |
| LANGUAGE SQL |
| AS $$ |
| SELECT 1, 2; |
| $$; |
| CALL ptest4a(NULL, NULL); |
| a | b |
| ---+--- |
| 1 | 2 |
| (1 row) |
| |
| CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int) |
| LANGUAGE SQL |
| AS $$ |
| CALL ptest4a(a, b); -- error, not supported |
| $$; |
| ERROR: calling procedures with output arguments is not supported in SQL functions |
| CONTEXT: SQL function "ptest4b" |
| -- we used to get confused by a single output argument that is composite |
| CREATE PROCEDURE ptest4c(INOUT comp int8_tbl) |
| LANGUAGE SQL |
| AS $$ |
| SELECT ROW(1, 2); |
| $$; |
| CALL ptest4c(NULL); |
| comp |
| ------- |
| (1,2) |
| (1 row) |
| |
| DROP PROCEDURE ptest4a, ptest4c; |
| -- named and default parameters |
| CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100) |
| LANGUAGE SQL |
| AS $$ |
| INSERT INTO cp_test VALUES(a, b); |
| INSERT INTO cp_test VALUES(c, b); |
| $$; |
| TRUNCATE cp_test; |
| CALL ptest5(10, 'Hello', 20); |
| CALL ptest5(10, 'Hello'); |
| CALL ptest5(10, b => 'Hello'); |
| CALL ptest5(b => 'Hello', a => 10); |
| SELECT * FROM cp_test; |
| a | b |
| -----+------- |
| 10 | Hello |
| 20 | Hello |
| 10 | Hello |
| 100 | Hello |
| 10 | Hello |
| 100 | Hello |
| 10 | Hello |
| 100 | Hello |
| (8 rows) |
| |
| -- polymorphic types |
| CREATE PROCEDURE ptest6(a int, b anyelement) |
| LANGUAGE SQL |
| AS $$ |
| SELECT NULL::int; |
| $$; |
| CALL ptest6(1, 2); |
| CREATE PROCEDURE ptest6a(inout a anyelement, out b anyelement) |
| LANGUAGE SQL |
| AS $$ |
| SELECT $1, $1; |
| $$; |
| CALL ptest6a(1, null); |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| CALL ptest6a(1.1, null); |
| a | b |
| -----+----- |
| 1.1 | 1.1 |
| (1 row) |
| |
| CREATE PROCEDURE ptest6b(a anyelement, out b anyelement, out c anyarray) |
| LANGUAGE SQL |
| AS $$ |
| SELECT $1, array[$1]; |
| $$; |
| CALL ptest6b(1, null, null); |
| b | c |
| ---+----- |
| 1 | {1} |
| (1 row) |
| |
| CALL ptest6b(1.1, null, null); |
| b | c |
| -----+------- |
| 1.1 | {1.1} |
| (1 row) |
| |
| CREATE PROCEDURE ptest6c(inout a anyelement, inout b anyelement) |
| LANGUAGE SQL |
| AS $$ |
| SELECT $1, 1; |
| $$; |
| CALL ptest6c(1, null); |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| CALL ptest6c(1.1, null); -- fails before v13 |
| a | b |
| -----+--- |
| 1.1 | 1 |
| (1 row) |
| |
| -- collation assignment |
| CREATE PROCEDURE ptest7(a text, b text) |
| LANGUAGE SQL |
| AS $$ |
| SELECT a = b; |
| $$; |
| CALL ptest7(least('a', 'b'), 'a'); |
| -- empty body |
| CREATE PROCEDURE ptest8(x text) |
| BEGIN ATOMIC |
| END; |
| \df ptest8 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+--------+------------------+---------------------+------ |
| public | ptest8 | | IN x text | proc |
| (1 row) |
| |
| SELECT pg_get_functiondef('ptest8'::regproc); |
| pg_get_functiondef |
| ------------------------------------------------------ |
| CREATE OR REPLACE PROCEDURE public.ptest8(IN x text)+ |
| LANGUAGE sql + |
| BEGIN ATOMIC + |
| END + |
| |
| (1 row) |
| |
| CALL ptest8(''); |
| -- OUT parameters |
| CREATE PROCEDURE ptest9(OUT a int) |
| LANGUAGE SQL |
| AS $$ |
| INSERT INTO cp_test VALUES (1, 'a'); |
| SELECT 1; |
| $$; |
| -- standard way to do a call: |
| CALL ptest9(NULL); |
| a |
| --- |
| 1 |
| (1 row) |
| |
| -- you can write an expression, but it's not evaluated |
| CALL ptest9(1/0); -- no error |
| a |
| --- |
| 1 |
| (1 row) |
| |
| -- ... and it had better match the type of the parameter |
| CALL ptest9(1./0.); -- error |
| ERROR: procedure ptest9(numeric) does not exist |
| LINE 1: CALL ptest9(1./0.); |
| ^ |
| HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. |
| -- check named-parameter matching |
| CREATE PROCEDURE ptest10(OUT a int, IN b int, IN c int) |
| LANGUAGE SQL AS $$ SELECT b - c $$; |
| CALL ptest10(null, 7, 4); |
| a |
| --- |
| 3 |
| (1 row) |
| |
| CALL ptest10(a => null, b => 8, c => 2); |
| a |
| --- |
| 6 |
| (1 row) |
| |
| CALL ptest10(null, 7, c => 2); |
| a |
| --- |
| 5 |
| (1 row) |
| |
| CALL ptest10(null, c => 4, b => 11); |
| a |
| --- |
| 7 |
| (1 row) |
| |
| CALL ptest10(b => 8, c => 2, a => 0); |
| a |
| --- |
| 6 |
| (1 row) |
| |
| CREATE PROCEDURE ptest11(a OUT int, VARIADIC b int[]) LANGUAGE SQL |
| AS $$ SELECT b[1] + b[2] $$; |
| CALL ptest11(null, 11, 12, 13); |
| a |
| ---- |
| 23 |
| (1 row) |
| |
| -- check resolution of ambiguous DROP commands |
| CREATE PROCEDURE ptest10(IN a int, IN b int, IN c int) |
| LANGUAGE SQL AS $$ SELECT a + b - c $$; |
| \df ptest10 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+---------+------------------+-------------------------------------------+------ |
| public | ptest10 | | IN a integer, IN b integer, IN c integer | proc |
| public | ptest10 | | OUT a integer, IN b integer, IN c integer | proc |
| (2 rows) |
| |
| drop procedure ptest10; -- fail |
| ERROR: procedure name "ptest10" is not unique |
| HINT: Specify the argument list to select the procedure unambiguously. |
| drop procedure ptest10(int, int, int); -- fail |
| ERROR: procedure name "ptest10" is not unique |
| begin; |
| drop procedure ptest10(out int, int, int); |
| \df ptest10 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+---------+------------------+------------------------------------------+------ |
| public | ptest10 | | IN a integer, IN b integer, IN c integer | proc |
| (1 row) |
| |
| drop procedure ptest10(int, int, int); -- now this would work |
| rollback; |
| begin; |
| drop procedure ptest10(in int, int, int); |
| \df ptest10 |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type |
| --------+---------+------------------+-------------------------------------------+------ |
| public | ptest10 | | OUT a integer, IN b integer, IN c integer | proc |
| (1 row) |
| |
| drop procedure ptest10(int, int, int); -- now this would work |
| rollback; |
| -- various error cases |
| CALL version(); -- error: not a procedure |
| ERROR: version() is not a procedure |
| LINE 1: CALL version(); |
| ^ |
| HINT: To call a function, use SELECT. |
| CALL sum(1); -- error: not a procedure |
| ERROR: sum(integer) is not a procedure |
| LINE 1: CALL sum(1); |
| ^ |
| HINT: To call a function, use SELECT. |
| CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; |
| ERROR: invalid attribute in procedure definition |
| LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT I... |
| ^ |
| CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; |
| ERROR: invalid attribute in procedure definition |
| LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT I... |
| ^ |
| CREATE PROCEDURE ptestx(a VARIADIC int[], b OUT int) LANGUAGE SQL |
| AS $$ SELECT a[1] $$; |
| ERROR: VARIADIC parameter must be the last parameter |
| CREATE PROCEDURE ptestx(a int DEFAULT 42, b OUT int) LANGUAGE SQL |
| AS $$ SELECT a $$; |
| ERROR: procedure OUT parameters cannot appear after one with a default value |
| ALTER PROCEDURE ptest1(text) STRICT; |
| ERROR: invalid attribute in procedure definition |
| LINE 1: ALTER PROCEDURE ptest1(text) STRICT; |
| ^ |
| ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function |
| ERROR: ptest1(text) is not a function |
| ALTER PROCEDURE cp_testfunc1(int) VOLATILE; -- error: not a procedure |
| ERROR: cp_testfunc1(integer) is not a procedure |
| ALTER PROCEDURE nonexistent() VOLATILE; |
| ERROR: procedure nonexistent() does not exist |
| DROP FUNCTION ptest1(text); -- error: not a function |
| ERROR: ptest1(text) is not a function |
| DROP PROCEDURE cp_testfunc1(int); -- error: not a procedure |
| ERROR: cp_testfunc1(integer) is not a procedure |
| DROP PROCEDURE nonexistent(); |
| ERROR: procedure nonexistent() does not exist |
| -- privileges |
| CREATE USER regress_cp_user1; |
| GRANT INSERT ON cp_test TO regress_cp_user1; |
| REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC; |
| SET ROLE regress_cp_user1; |
| CALL ptest1('a'); -- error |
| ERROR: permission denied for procedure ptest1 |
| RESET ROLE; |
| GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_cp_user1; |
| SET ROLE regress_cp_user1; |
| CALL ptest1('a'); -- ok |
| RESET ROLE; |
| -- ROUTINE syntax |
| ALTER ROUTINE cp_testfunc1(int) RENAME TO cp_testfunc1a; |
| ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1; |
| ALTER ROUTINE ptest1(text) RENAME TO ptest1a; |
| ALTER ROUTINE ptest1a RENAME TO ptest1; |
| DROP ROUTINE cp_testfunc1(int); |
| -- cleanup |
| DROP PROCEDURE ptest1; |
| DROP PROCEDURE ptest1s; |
| DROP PROCEDURE ptest2; |
| DROP TABLE cp_test; |
| DROP USER regress_cp_user1; |
