summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/create_procedure.out92
-rw-r--r--src/test/regress/expected/object_address.out15
-rw-r--r--src/test/regress/expected/plpgsql.out41
-rw-r--r--src/test/regress/expected/polymorphism.out16
-rw-r--r--src/test/regress/expected/privileges.out128
-rw-r--r--src/test/regress/parallel_schedule2
-rw-r--r--src/test/regress/serial_schedule1
-rw-r--r--src/test/regress/sql/create_procedure.sql79
-rw-r--r--src/test/regress/sql/object_address.sql4
-rw-r--r--src/test/regress/sql/plpgsql.sql49
-rw-r--r--src/test/regress/sql/privileges.sql55
11 files changed, 461 insertions, 21 deletions
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
new file mode 100644
index 00000000000..5538ef2f2be
--- /dev/null
+++ b/src/test/regress/expected/create_procedure.out
@@ -0,0 +1,92 @@
+CALL nonexistent(); -- error
+ERROR: function nonexistent() does not exist
+LINE 1: CALL nonexistent();
+ ^
+HINT: No function 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 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);
+$$;
+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
+\df ptest1
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+--------+------------------+---------------------+------
+ public | ptest1 | | x text | proc
+(1 row)
+
+SELECT * FROM cp_test ORDER BY a;
+ a | b
+---+---
+ 1 | a
+(1 row)
+
+CREATE PROCEDURE ptest2()
+LANGUAGE SQL
+AS $$
+SELECT 5;
+$$;
+CALL ptest2();
+-- various error cases
+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 parameters
+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 testfunc1(int) VOLATILE; -- error: not a procedure
+ERROR: 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 testfunc1(int); -- error: not a procedure
+ERROR: testfunc1(integer) is not a procedure
+DROP PROCEDURE nonexistent();
+ERROR: procedure nonexistent() does not exist
+-- privileges
+CREATE USER regress_user1;
+GRANT INSERT ON cp_test TO regress_user1;
+REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC;
+SET ROLE regress_user1;
+CALL ptest1('a'); -- error
+ERROR: permission denied for function ptest1
+RESET ROLE;
+GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_user1;
+SET ROLE regress_user1;
+CALL ptest1('a'); -- ok
+RESET ROLE;
+-- ROUTINE syntax
+ALTER ROUTINE testfunc1(int) RENAME TO testfunc1a;
+ALTER ROUTINE testfunc1a RENAME TO testfunc1;
+ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
+ALTER ROUTINE ptest1a RENAME TO ptest1;
+DROP ROUTINE testfunc1(int);
+-- cleanup
+DROP PROCEDURE ptest1;
+DROP PROCEDURE ptest2;
+DROP TABLE cp_test;
+DROP USER regress_user1;
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 1fdadbc9ef6..bfd9d54c119 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -29,6 +29,7 @@ CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0);
CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$;
CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig();
CREATE POLICY genpol ON addr_nsp.gentable;
+CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$;
CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw;
CREATE USER MAPPING FOR regress_addr_user SERVER "integer";
ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user;
@@ -88,7 +89,7 @@ BEGIN
('table'), ('index'), ('sequence'), ('view'),
('materialized view'), ('foreign table'),
('table column'), ('foreign table column'),
- ('aggregate'), ('function'), ('type'), ('cast'),
+ ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'),
('table constraint'), ('domain constraint'), ('conversion'), ('default value'),
('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'),
('text search parser'), ('text search dictionary'),
@@ -171,6 +172,12 @@ WARNING: error for function,{addr_nsp,zwei},{}: function addr_nsp.zwei() does n
WARNING: error for function,{addr_nsp,zwei},{integer}: function addr_nsp.zwei(integer) does not exist
WARNING: error for function,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
WARNING: error for function,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
+WARNING: error for procedure,{eins},{}: procedure eins() does not exist
+WARNING: error for procedure,{eins},{integer}: procedure eins(integer) does not exist
+WARNING: error for procedure,{addr_nsp,zwei},{}: procedure addr_nsp.zwei() does not exist
+WARNING: error for procedure,{addr_nsp,zwei},{integer}: procedure addr_nsp.zwei(integer) does not exist
+WARNING: error for procedure,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
+WARNING: error for procedure,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
WARNING: error for type,{eins},{}: type "eins" does not exist
WARNING: error for type,{eins},{integer}: type "eins" does not exist
WARNING: error for type,{addr_nsp,zwei},{}: name list length must be exactly 1
@@ -371,6 +378,7 @@ WITH objects (type, name, args) AS (VALUES
('foreign table column', '{addr_nsp, genftable, a}', '{}'),
('aggregate', '{addr_nsp, genaggr}', '{int4}'),
('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'),
+ ('procedure', '{addr_nsp, proc}', '{int4}'),
('type', '{pg_catalog._int4}', '{}'),
('type', '{addr_nsp.gendomain}', '{}'),
('type', '{addr_nsp.gencomptype}', '{}'),
@@ -431,6 +439,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
type | addr_nsp | gendomain | addr_nsp.gendomain | t
function | pg_catalog | | pg_catalog.pg_identify_object(pg_catalog.oid,pg_catalog.oid,integer) | t
aggregate | addr_nsp | | addr_nsp.genaggr(integer) | t
+ procedure | addr_nsp | | addr_nsp.proc(integer) | t
sequence | addr_nsp | gentable_a_seq | addr_nsp.gentable_a_seq | t
table | addr_nsp | gentable | addr_nsp.gentable | t
table column | addr_nsp | gentable | addr_nsp.gentable.b | t
@@ -469,7 +478,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
subscription | | addr_sub | addr_sub | t
publication | | addr_pub | addr_pub | t
publication relation | | | gentable in publication addr_pub | t
-(46 rows)
+(47 rows)
---
--- Cleanup resources
@@ -480,6 +489,6 @@ NOTICE: drop cascades to 4 other objects
DROP PUBLICATION addr_pub;
DROP SUBSCRIPTION addr_sub;
DROP SCHEMA addr_nsp CASCADE;
-NOTICE: drop cascades to 12 other objects
+NOTICE: drop cascades to 13 other objects
DROP OWNED BY regress_addr_user;
DROP USER regress_addr_user;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index bb3532676bd..d6e5bc33536 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -6040,3 +6040,44 @@ END; $$ LANGUAGE plpgsql;
ERROR: "x" is not a scalar variable
LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
^
+--
+-- Procedures
+--
+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;
+$$;
+CALL test_proc2();
+ERROR: cannot return a value from a procedure
+CONTEXT: PL/pgSQL function test_proc2() while casting return value to function's return type
+CREATE TABLE proc_test1 (a int);
+CREATE PROCEDURE test_proc3(x int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO proc_test1 VALUES (x);
+END;
+$$;
+CALL test_proc3(55);
+SELECT * FROM proc_test1;
+ a
+----
+ 55
+(1 row)
+
+DROP PROCEDURE test_proc1;
+DROP PROCEDURE test_proc2;
+DROP PROCEDURE test_proc3;
+DROP TABLE proc_test1;
diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out
index 91cfb743b63..66e35a6a5c2 100644
--- a/src/test/regress/expected/polymorphism.out
+++ b/src/test/regress/expected/polymorphism.out
@@ -915,10 +915,10 @@ select dfunc();
-- verify it lists properly
\df dfunc
- List of functions
- Schema | Name | Result data type | Argument data types | Type
---------+-------+------------------+-----------------------------------------------------------+--------
- public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | normal
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+-----------------------------------------------------------+------
+ public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | func
(1 row)
drop function dfunc(int, int);
@@ -1083,10 +1083,10 @@ $$ select array_upper($1, 1) $$ language sql;
ERROR: cannot remove parameter defaults from existing function
HINT: Use DROP FUNCTION dfunc(integer[]) first.
\df dfunc
- List of functions
- Schema | Name | Result data type | Argument data types | Type
---------+-------+------------------+-------------------------------------------------+--------
- public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | normal
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+-------------------------------------------------+------
+ public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | func
(1 row)
drop function dfunc(a variadic int[]);
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 771971a095f..e6994f04905 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -651,13 +651,25 @@ GRANT USAGE ON LANGUAGE sql TO regress_user2; -- fail
WARNING: no privileges were granted for "sql"
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
-REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
-GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regress_user2;
+CREATE AGGREGATE testagg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testproc1(int) AS 'select $1;' LANGUAGE sql;
+REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) TO regress_user2;
+REVOKE ALL ON FUNCTION testproc1(int) FROM PUBLIC; -- fail, not a function
+ERROR: testproc1(integer) is not a function
+REVOKE ALL ON PROCEDURE testproc1(int) FROM PUBLIC;
+GRANT EXECUTE ON PROCEDURE testproc1(int) TO regress_user2;
GRANT USAGE ON FUNCTION testfunc1(int) TO regress_user3; -- semantic error
ERROR: invalid privilege type USAGE for function
+GRANT USAGE ON FUNCTION testagg1(int) TO regress_user3; -- semantic error
+ERROR: invalid privilege type USAGE for function
+GRANT USAGE ON PROCEDURE testproc1(int) TO regress_user3; -- semantic error
+ERROR: invalid privilege type USAGE for procedure
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regress_user4;
GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regress_user4;
ERROR: function testfunc_nosuch(integer) does not exist
+GRANT ALL PRIVILEGES ON FUNCTION testagg1(int) TO regress_user4;
+GRANT ALL PRIVILEGES ON PROCEDURE testproc1(int) TO regress_user4;
CREATE FUNCTION testfunc4(boolean) RETURNS text
AS 'select col1 from atest2 where col2 = $1;'
LANGUAGE sql SECURITY DEFINER;
@@ -671,9 +683,20 @@ SELECT testfunc1(5), testfunc2(5); -- ok
CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
ERROR: permission denied for language sql
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+ testagg1
+----------
+ 6
+(1 row)
+
+CALL testproc1(6); -- ok
SET SESSION AUTHORIZATION regress_user3;
SELECT testfunc1(5); -- fail
ERROR: permission denied for function testfunc1
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail
+ERROR: permission denied for function testagg1
+CALL testproc1(6); -- fail
+ERROR: permission denied for function testproc1
SELECT col1 FROM atest2 WHERE col2 = true; -- fail
ERROR: permission denied for relation atest2
SELECT testfunc4(true); -- ok
@@ -689,8 +712,19 @@ SELECT testfunc1(5); -- ok
10
(1 row)
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+ testagg1
+----------
+ 6
+(1 row)
+
+CALL testproc1(6); -- ok
DROP FUNCTION testfunc1(int); -- fail
ERROR: must be owner of function testfunc1
+DROP AGGREGATE testagg1(int); -- fail
+ERROR: must be owner of function testagg1
+DROP PROCEDURE testproc1(int); -- fail
+ERROR: must be owner of function testproc1
\c -
DROP FUNCTION testfunc1(int); -- ok
-- restore to sanity
@@ -1537,22 +1571,54 @@ SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no
SET ROLE regress_user1;
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no
has_function_privilege
------------------------
f
(1 row)
-ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public;
+SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- no
+ has_function_privilege
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- no
+ has_function_privilege
+------------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
DROP FUNCTION testns.foo();
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+DROP AGGREGATE testns.agg1(int);
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+DROP PROCEDURE testns.bar();
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes
has_function_privilege
------------------------
t
(1 row)
+SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here)
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
DROP FUNCTION testns.foo();
+DROP AGGREGATE testns.agg1(int);
+DROP PROCEDURE testns.bar();
ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public;
CREATE DOMAIN testns.testdomain1 AS int;
SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- no
@@ -1631,12 +1697,26 @@ SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false
(1 row)
CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+CREATE AGGREGATE testns.testagg(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.testproc(int) AS 'select 3' LANGUAGE sql;
SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default
has_function_privilege
------------------------
t
(1 row)
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true by default
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true by default
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false
has_function_privilege
@@ -1644,9 +1724,47 @@ SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'
f
(1 row)
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- false
+ has_function_privilege
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- still true, not a function
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- now false
+ has_function_privilege
+------------------------
+ f
+(1 row)
+
+GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
+SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
\set VERBOSITY terse \\ -- suppress cascade details
DROP SCHEMA testns CASCADE;
-NOTICE: drop cascades to 3 other objects
+NOTICE: drop cascades to 5 other objects
\set VERBOSITY default
-- Change owner of the schema & and rename of new schema owner
\c -
@@ -1729,8 +1847,10 @@ drop table dep_priv_test;
-- clean up
\c
drop sequence x_seq;
+DROP AGGREGATE testagg1(int);
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);
+DROP PROCEDURE testproc1(int);
DROP VIEW atestv0;
DROP VIEW atestv1;
DROP VIEW atestv2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 892a214f2f3..e224977791e 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -53,7 +53,7 @@ test: copy copyselect copydml
# ----------
# More groups of parallel tests
# ----------
-test: create_misc create_operator
+test: create_misc create_operator create_procedure
# These depend on the above two
test: create_index create_view
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 15a1f861a94..9fc5f1a2688 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -63,6 +63,7 @@ test: copyselect
test: copydml
test: create_misc
test: create_operator
+test: create_procedure
test: create_index
test: create_view
test: create_aggregate
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
new file mode 100644
index 00000000000..f09ba2ad30c
--- /dev/null
+++ b/src/test/regress/sql/create_procedure.sql
@@ -0,0 +1,79 @@
+CALL nonexistent(); -- error
+CALL random(); -- error
+
+CREATE FUNCTION 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);
+$$;
+
+SELECT ptest1('x'); -- error
+CALL ptest1('a'); -- ok
+
+\df ptest1
+
+SELECT * FROM cp_test ORDER BY a;
+
+
+CREATE PROCEDURE ptest2()
+LANGUAGE SQL
+AS $$
+SELECT 5;
+$$;
+
+CALL ptest2();
+
+
+-- various error cases
+
+CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
+CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
+CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
+
+ALTER PROCEDURE ptest1(text) STRICT;
+ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function
+ALTER PROCEDURE testfunc1(int) VOLATILE; -- error: not a procedure
+ALTER PROCEDURE nonexistent() VOLATILE;
+
+DROP FUNCTION ptest1(text); -- error: not a function
+DROP PROCEDURE testfunc1(int); -- error: not a procedure
+DROP PROCEDURE nonexistent();
+
+
+-- privileges
+
+CREATE USER regress_user1;
+GRANT INSERT ON cp_test TO regress_user1;
+REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC;
+SET ROLE regress_user1;
+CALL ptest1('a'); -- error
+RESET ROLE;
+GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_user1;
+SET ROLE regress_user1;
+CALL ptest1('a'); -- ok
+RESET ROLE;
+
+
+-- ROUTINE syntax
+
+ALTER ROUTINE testfunc1(int) RENAME TO testfunc1a;
+ALTER ROUTINE testfunc1a RENAME TO testfunc1;
+
+ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
+ALTER ROUTINE ptest1a RENAME TO ptest1;
+
+DROP ROUTINE testfunc1(int);
+
+
+-- cleanup
+
+DROP PROCEDURE ptest1;
+DROP PROCEDURE ptest2;
+
+DROP TABLE cp_test;
+
+DROP USER regress_user1;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 63821b8008b..55faa71edfb 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -32,6 +32,7 @@ CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0);
CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$;
CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig();
CREATE POLICY genpol ON addr_nsp.gentable;
+CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$;
CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw;
CREATE USER MAPPING FOR regress_addr_user SERVER "integer";
ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user;
@@ -81,7 +82,7 @@ BEGIN
('table'), ('index'), ('sequence'), ('view'),
('materialized view'), ('foreign table'),
('table column'), ('foreign table column'),
- ('aggregate'), ('function'), ('type'), ('cast'),
+ ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'),
('table constraint'), ('domain constraint'), ('conversion'), ('default value'),
('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'),
('text search parser'), ('text search dictionary'),
@@ -147,6 +148,7 @@ WITH objects (type, name, args) AS (VALUES
('foreign table column', '{addr_nsp, genftable, a}', '{}'),
('aggregate', '{addr_nsp, genaggr}', '{int4}'),
('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'),
+ ('procedure', '{addr_nsp, proc}', '{int4}'),
('type', '{pg_catalog._int4}', '{}'),
('type', '{addr_nsp.gendomain}', '{}'),
('type', '{addr_nsp.gencomptype}', '{}'),
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 6620ea61729..1c355132b77 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4820,3 +4820,52 @@ BEGIN
GET DIAGNOSTICS x = ROW_COUNT;
RETURN;
END; $$ LANGUAGE plpgsql;
+
+
+--
+-- Procedures
+--
+
+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;
+$$;
+
+CALL test_proc2();
+
+
+CREATE TABLE proc_test1 (a int);
+
+CREATE PROCEDURE test_proc3(x int)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO proc_test1 VALUES (x);
+END;
+$$;
+
+CALL test_proc3(55);
+
+SELECT * FROM proc_test1;
+
+
+DROP PROCEDURE test_proc1;
+DROP PROCEDURE test_proc2;
+DROP PROCEDURE test_proc3;
+
+DROP TABLE proc_test1;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index a900ba2f849..ea8dd028cd0 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -442,12 +442,21 @@ SET SESSION AUTHORIZATION regress_user1;
GRANT USAGE ON LANGUAGE sql TO regress_user2; -- fail
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
-
-REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
-GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regress_user2;
+CREATE AGGREGATE testagg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testproc1(int) AS 'select $1;' LANGUAGE sql;
+
+REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) TO regress_user2;
+REVOKE ALL ON FUNCTION testproc1(int) FROM PUBLIC; -- fail, not a function
+REVOKE ALL ON PROCEDURE testproc1(int) FROM PUBLIC;
+GRANT EXECUTE ON PROCEDURE testproc1(int) TO regress_user2;
GRANT USAGE ON FUNCTION testfunc1(int) TO regress_user3; -- semantic error
+GRANT USAGE ON FUNCTION testagg1(int) TO regress_user3; -- semantic error
+GRANT USAGE ON PROCEDURE testproc1(int) TO regress_user3; -- semantic error
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regress_user4;
GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regress_user4;
+GRANT ALL PRIVILEGES ON FUNCTION testagg1(int) TO regress_user4;
+GRANT ALL PRIVILEGES ON PROCEDURE testproc1(int) TO regress_user4;
CREATE FUNCTION testfunc4(boolean) RETURNS text
AS 'select col1 from atest2 where col2 = $1;'
@@ -457,16 +466,24 @@ GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regress_user3;
SET SESSION AUTHORIZATION regress_user2;
SELECT testfunc1(5), testfunc2(5); -- ok
CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+CALL testproc1(6); -- ok
SET SESSION AUTHORIZATION regress_user3;
SELECT testfunc1(5); -- fail
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail
+CALL testproc1(6); -- fail
SELECT col1 FROM atest2 WHERE col2 = true; -- fail
SELECT testfunc4(true); -- ok
SET SESSION AUTHORIZATION regress_user4;
SELECT testfunc1(5); -- ok
+SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+CALL testproc1(6); -- ok
DROP FUNCTION testfunc1(int); -- fail
+DROP AGGREGATE testagg1(int); -- fail
+DROP PROCEDURE testproc1(int); -- fail
\c -
@@ -931,17 +948,29 @@ SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no
SET ROLE regress_user1;
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no
+SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- no
+SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- no
-ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public;
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
DROP FUNCTION testns.foo();
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+DROP AGGREGATE testns.agg1(int);
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+DROP PROCEDURE testns.bar();
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes
+SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes
+SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here)
DROP FUNCTION testns.foo();
+DROP AGGREGATE testns.agg1(int);
+DROP PROCEDURE testns.bar();
ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public;
@@ -995,12 +1024,28 @@ SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false
SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false
CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+CREATE AGGREGATE testns.testagg(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.testproc(int) AS 'select 3' LANGUAGE sql;
SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true by default
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true by default
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- false
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- still true, not a function
+
+REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
+
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- now false
+
+GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
+
+SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true
+SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true
+SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true
\set VERBOSITY terse \\ -- suppress cascade details
DROP SCHEMA testns CASCADE;
@@ -1064,8 +1109,10 @@ drop table dep_priv_test;
drop sequence x_seq;
+DROP AGGREGATE testagg1(int);
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);
+DROP PROCEDURE testproc1(int);
DROP VIEW atestv0;
DROP VIEW atestv1;
generated by cgit v1.2.3 (git 2.46.0) at 2025-11-06 22:30:18 +0000