diff options
Diffstat (limited to 'src/test/regress/expected/create_procedure.out')
| -rw-r--r-- | src/test/regress/expected/create_procedure.out | 209 |
1 files changed, 209 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out new file mode 100644 index 0000000..211a42c --- /dev/null +++ b/src/test/regress/expected/create_procedure.out @@ -0,0 +1,209 @@ +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 | | x text | proc +(1 row) + +SELECT pg_get_functiondef('ptest1'::regproc); + pg_get_functiondef +--------------------------------------------------- + CREATE OR REPLACE PROCEDURE public.ptest1(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 | | 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) + +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" +DROP PROCEDURE ptest4a; +-- 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); +-- collation assignment +CREATE PROCEDURE ptest7(a text, b text) +LANGUAGE SQL +AS $$ +SELECT a = b; +$$; +CALL ptest7(least('a', 'b'), 'a'); +-- 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(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +ERROR: procedures cannot have OUT arguments +HINT: INOUT arguments are permitted. +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 ptest2; +DROP TABLE cp_test; +DROP USER regress_cp_user1; |