summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/create_procedure.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/create_procedure.out')
-rw-r--r--src/test/regress/expected/create_procedure.out383
1 files changed, 383 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..f2a677f
--- /dev/null
+++ b/src/test/regress/expected/create_procedure.out
@@ -0,0 +1,383 @@
+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"
+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');
+-- 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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 22:46:31 +0000