aboutsummaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/create_function_3.out463
-rw-r--r--src/test/regress/expected/select_views.out20
-rw-r--r--src/test/regress/expected/select_views_1.out20
-rw-r--r--src/test/regress/parallel_schedule1
-rw-r--r--src/test/regress/serial_schedule1
-rw-r--r--src/test/regress/sql/create_function_3.sql145
-rw-r--r--src/test/regress/sql/select_views.sql3
7 files changed, 632 insertions, 21 deletions
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
new file mode 100644
index 00000000000..a8c0526c22f
--- /dev/null
+++ b/src/test/regress/expected/create_function_3.out
@@ -0,0 +1,463 @@
+--
+-- CREATE FUNCTION
+--
+-- sanity check of pg_proc catalog to the given parameters
+--
+CREATE SCHEMA temp_func_test;
+SET search_path TO temp_func_test, public;
+--
+-- ARGUMENT and RETURN TYPES
+--
+CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
+CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
+ AS 'SELECT $1[0]::int';
+CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT false';
+SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
+ WHERE oid in ('functest_A_1'::regproc,
+ 'functest_A_2'::regproc,
+ 'functest_A_3'::regproc);
+ proname | prorettype | proargtypes
+--------------+------------+-------------------
+ functest_a_1 | boolean | [0:1]={text,date}
+ functest_a_2 | integer | [0:0]={text[]}
+ functest_a_3 | boolean | {}
+(3 rows)
+
+--
+-- IMMUTABLE | STABLE | VOLATILE
+--
+CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 0';
+CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
+ IMMUTABLE AS 'SELECT $1 > 0';
+CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
+ STABLE AS 'SELECT $1 = 0';
+CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
+ VOLATILE AS 'SELECT $1 < 0';
+SELECT proname, provolatile FROM pg_proc
+ WHERE oid in ('functest_B_1'::regproc,
+ 'functest_B_2'::regproc,
+ 'functest_B_3'::regproc,
+ 'functest_B_4'::regproc);
+ proname | provolatile
+--------------+-------------
+ functest_b_1 | v
+ functest_b_2 | i
+ functest_b_3 | s
+ functest_b_4 | v
+(4 rows)
+
+ALTER FUNCTION functest_B_2(int) VOLATILE;
+ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect
+SELECT proname, provolatile FROM pg_proc
+ WHERE oid in ('functest_B_1'::regproc,
+ 'functest_B_2'::regproc,
+ 'functest_B_3'::regproc,
+ 'functest_B_4'::regproc);
+ proname | provolatile
+--------------+-------------
+ functest_b_1 | v
+ functest_b_2 | v
+ functest_b_3 | s
+ functest_b_4 | v
+(4 rows)
+
+--
+-- SECURITY DEFINER | INVOKER
+--
+CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 0';
+CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
+ SECURITY DEFINER AS 'SELECT $1 = 0';
+CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
+ SECURITY INVOKER AS 'SELECT $1 < 0';
+SELECT proname, prosecdef FROM pg_proc
+ WHERE oid in ('functext_C_1'::regproc,
+ 'functext_C_2'::regproc,
+ 'functext_C_3'::regproc);
+ proname | prosecdef
+--------------+-----------
+ functext_c_1 | f
+ functext_c_2 | t
+ functext_c_3 | f
+(3 rows)
+
+ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_C_2(int) SECURITY INVOKER;
+ALTER FUNCTION functext_C_3(int) SECURITY DEFINER;
+SELECT proname, prosecdef FROM pg_proc
+ WHERE oid in ('functext_C_1'::regproc,
+ 'functext_C_2'::regproc,
+ 'functext_C_3'::regproc);
+ proname | prosecdef
+--------------+-----------
+ functext_c_1 | f
+ functext_c_2 | f
+ functext_c_3 | t
+(3 rows)
+
+--
+-- COST
+--
+CREATE FUNCTION functext_D_1(int,int) RETURNS int LANGUAGE 'sql'
+ AS 'SELECT $1 + $2';
+CREATE FUNCTION functext_D_2(int,int) RETURNS int LANGUAGE 'internal'
+ AS 'int4pl';
+CREATE FUNCTION functext_D_3(int,int) RETURNS int LANGUAGE 'sql'
+ COST 500 AS 'SELECT $1 * $2';
+CREATE FUNCTION functext_D_4(int,int) RETURNS int LANGUAGE 'sql'
+ COST 0 AS 'SELECT $1 / $2'; -- Error
+ERROR: COST must be positive
+SELECT proname, procost FROM pg_proc
+ WHERE oid in ('functext_D_1'::regproc,
+ 'functext_D_2'::regproc,
+ 'functext_D_3'::regproc);
+ proname | procost
+--------------+---------
+ functext_d_1 | 100
+ functext_d_2 | 1
+ functext_d_3 | 500
+(3 rows)
+
+ALTER FUNCTION functext_D_1(int,int) STABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_D_2(int,int) COST 50;
+ALTER FUNCTION functext_D_3(int,int) COST 0.0001;
+SELECT proname, procost FROM pg_proc
+ WHERE oid in ('functext_D_1'::regproc,
+ 'functext_D_2'::regproc,
+ 'functext_D_3'::regproc);
+ proname | procost
+--------------+---------
+ functext_d_1 | 100
+ functext_d_2 | 50
+ functext_d_3 | 0.0001
+(3 rows)
+
+--
+-- LEAKPROOF
+--
+CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 100';
+CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
+ LEAKPROOF AS 'SELECT $1 > 100';
+SELECT proname, proleakproof FROM pg_proc
+ WHERE oid in ('functext_E_1'::regproc,
+ 'functext_E_2'::regproc);
+ proname | proleakproof
+--------------+--------------
+ functext_e_1 | f
+ functext_e_2 | t
+(2 rows)
+
+ALTER FUNCTION functext_E_1(int) LEAKPROOF;
+ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect
+SELECT proname, proleakproof FROM pg_proc
+ WHERE oid in ('functext_E_1'::regproc,
+ 'functext_E_2'::regproc);
+ proname | proleakproof
+--------------+--------------
+ functext_e_1 | t
+ functext_e_2 | t
+(2 rows)
+
+-- list of built-in leakproof functions
+SELECT proname, prorettype::regtype, proargtypes::regtype[]
+ FROM pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid
+ WHERE nspname = 'pg_catalog' AND proleakproof ORDER BY proname;
+ proname | prorettype | proargtypes
+----------------+------------+---------------------------------------------------------------------
+ abstimeeq | boolean | [0:1]={abstime,abstime}
+ abstimege | boolean | [0:1]={abstime,abstime}
+ abstimegt | boolean | [0:1]={abstime,abstime}
+ abstimele | boolean | [0:1]={abstime,abstime}
+ abstimelt | boolean | [0:1]={abstime,abstime}
+ abstimene | boolean | [0:1]={abstime,abstime}
+ biteq | boolean | [0:1]={bit,bit}
+ bitge | boolean | [0:1]={bit,bit}
+ bitgt | boolean | [0:1]={bit,bit}
+ bitle | boolean | [0:1]={bit,bit}
+ bitlt | boolean | [0:1]={bit,bit}
+ bitne | boolean | [0:1]={bit,bit}
+ booleq | boolean | [0:1]={boolean,boolean}
+ boolge | boolean | [0:1]={boolean,boolean}
+ boolgt | boolean | [0:1]={boolean,boolean}
+ boolle | boolean | [0:1]={boolean,boolean}
+ boollt | boolean | [0:1]={boolean,boolean}
+ boolne | boolean | [0:1]={boolean,boolean}
+ bpchareq | boolean | [0:1]={character,character}
+ bpcharne | boolean | [0:1]={character,character}
+ byteaeq | boolean | [0:1]={bytea,bytea}
+ byteage | boolean | [0:1]={bytea,bytea}
+ byteagt | boolean | [0:1]={bytea,bytea}
+ byteale | boolean | [0:1]={bytea,bytea}
+ bytealt | boolean | [0:1]={bytea,bytea}
+ byteane | boolean | [0:1]={bytea,bytea}
+ cash_eq | boolean | [0:1]={money,money}
+ cash_ge | boolean | [0:1]={money,money}
+ cash_gt | boolean | [0:1]={money,money}
+ cash_le | boolean | [0:1]={money,money}
+ cash_lt | boolean | [0:1]={money,money}
+ cash_ne | boolean | [0:1]={money,money}
+ chareq | boolean | [0:1]={"\"char\"","\"char\""}
+ charge | boolean | [0:1]={"\"char\"","\"char\""}
+ chargt | boolean | [0:1]={"\"char\"","\"char\""}
+ charle | boolean | [0:1]={"\"char\"","\"char\""}
+ charlt | boolean | [0:1]={"\"char\"","\"char\""}
+ charne | boolean | [0:1]={"\"char\"","\"char\""}
+ cideq | boolean | [0:1]={cid,cid}
+ circle_eq | boolean | [0:1]={circle,circle}
+ circle_ge | boolean | [0:1]={circle,circle}
+ circle_gt | boolean | [0:1]={circle,circle}
+ circle_le | boolean | [0:1]={circle,circle}
+ circle_lt | boolean | [0:1]={circle,circle}
+ circle_ne | boolean | [0:1]={circle,circle}
+ date_eq | boolean | [0:1]={date,date}
+ date_ge | boolean | [0:1]={date,date}
+ date_gt | boolean | [0:1]={date,date}
+ date_le | boolean | [0:1]={date,date}
+ date_lt | boolean | [0:1]={date,date}
+ date_ne | boolean | [0:1]={date,date}
+ float48eq | boolean | [0:1]={real,"double precision"}
+ float48ge | boolean | [0:1]={real,"double precision"}
+ float48gt | boolean | [0:1]={real,"double precision"}
+ float48le | boolean | [0:1]={real,"double precision"}
+ float48lt | boolean | [0:1]={real,"double precision"}
+ float48ne | boolean | [0:1]={real,"double precision"}
+ float4eq | boolean | [0:1]={real,real}
+ float4ge | boolean | [0:1]={real,real}
+ float4gt | boolean | [0:1]={real,real}
+ float4le | boolean | [0:1]={real,real}
+ float4lt | boolean | [0:1]={real,real}
+ float4ne | boolean | [0:1]={real,real}
+ float84eq | boolean | [0:1]={"double precision",real}
+ float84ge | boolean | [0:1]={"double precision",real}
+ float84gt | boolean | [0:1]={"double precision",real}
+ float84le | boolean | [0:1]={"double precision",real}
+ float84lt | boolean | [0:1]={"double precision",real}
+ float84ne | boolean | [0:1]={"double precision",real}
+ float8eq | boolean | [0:1]={"double precision","double precision"}
+ float8ge | boolean | [0:1]={"double precision","double precision"}
+ float8gt | boolean | [0:1]={"double precision","double precision"}
+ float8le | boolean | [0:1]={"double precision","double precision"}
+ float8lt | boolean | [0:1]={"double precision","double precision"}
+ float8ne | boolean | [0:1]={"double precision","double precision"}
+ int24eq | boolean | [0:1]={smallint,integer}
+ int24ge | boolean | [0:1]={smallint,integer}
+ int24gt | boolean | [0:1]={smallint,integer}
+ int24le | boolean | [0:1]={smallint,integer}
+ int24lt | boolean | [0:1]={smallint,integer}
+ int24ne | boolean | [0:1]={smallint,integer}
+ int28eq | boolean | [0:1]={smallint,bigint}
+ int28ge | boolean | [0:1]={smallint,bigint}
+ int28gt | boolean | [0:1]={smallint,bigint}
+ int28le | boolean | [0:1]={smallint,bigint}
+ int28lt | boolean | [0:1]={smallint,bigint}
+ int28ne | boolean | [0:1]={smallint,bigint}
+ int2eq | boolean | [0:1]={smallint,smallint}
+ int2ge | boolean | [0:1]={smallint,smallint}
+ int2gt | boolean | [0:1]={smallint,smallint}
+ int2le | boolean | [0:1]={smallint,smallint}
+ int2lt | boolean | [0:1]={smallint,smallint}
+ int2ne | boolean | [0:1]={smallint,smallint}
+ int42eq | boolean | [0:1]={integer,smallint}
+ int42ge | boolean | [0:1]={integer,smallint}
+ int42gt | boolean | [0:1]={integer,smallint}
+ int42le | boolean | [0:1]={integer,smallint}
+ int42lt | boolean | [0:1]={integer,smallint}
+ int42ne | boolean | [0:1]={integer,smallint}
+ int48eq | boolean | [0:1]={integer,bigint}
+ int48ge | boolean | [0:1]={integer,bigint}
+ int48gt | boolean | [0:1]={integer,bigint}
+ int48le | boolean | [0:1]={integer,bigint}
+ int48lt | boolean | [0:1]={integer,bigint}
+ int48ne | boolean | [0:1]={integer,bigint}
+ int4eq | boolean | [0:1]={integer,integer}
+ int4ge | boolean | [0:1]={integer,integer}
+ int4gt | boolean | [0:1]={integer,integer}
+ int4le | boolean | [0:1]={integer,integer}
+ int4lt | boolean | [0:1]={integer,integer}
+ int4ne | boolean | [0:1]={integer,integer}
+ int82eq | boolean | [0:1]={bigint,smallint}
+ int82ge | boolean | [0:1]={bigint,smallint}
+ int82gt | boolean | [0:1]={bigint,smallint}
+ int82le | boolean | [0:1]={bigint,smallint}
+ int82lt | boolean | [0:1]={bigint,smallint}
+ int82ne | boolean | [0:1]={bigint,smallint}
+ int84eq | boolean | [0:1]={bigint,integer}
+ int84ge | boolean | [0:1]={bigint,integer}
+ int84gt | boolean | [0:1]={bigint,integer}
+ int84le | boolean | [0:1]={bigint,integer}
+ int84lt | boolean | [0:1]={bigint,integer}
+ int84ne | boolean | [0:1]={bigint,integer}
+ int8eq | boolean | [0:1]={bigint,bigint}
+ int8ge | boolean | [0:1]={bigint,bigint}
+ int8gt | boolean | [0:1]={bigint,bigint}
+ int8le | boolean | [0:1]={bigint,bigint}
+ int8lt | boolean | [0:1]={bigint,bigint}
+ int8ne | boolean | [0:1]={bigint,bigint}
+ interval_eq | boolean | [0:1]={interval,interval}
+ interval_ge | boolean | [0:1]={interval,interval}
+ interval_gt | boolean | [0:1]={interval,interval}
+ interval_le | boolean | [0:1]={interval,interval}
+ interval_lt | boolean | [0:1]={interval,interval}
+ interval_ne | boolean | [0:1]={interval,interval}
+ lseg_eq | boolean | [0:1]={lseg,lseg}
+ lseg_ge | boolean | [0:1]={lseg,lseg}
+ lseg_gt | boolean | [0:1]={lseg,lseg}
+ lseg_le | boolean | [0:1]={lseg,lseg}
+ lseg_lt | boolean | [0:1]={lseg,lseg}
+ lseg_ne | boolean | [0:1]={lseg,lseg}
+ macaddr_eq | boolean | [0:1]={macaddr,macaddr}
+ macaddr_ge | boolean | [0:1]={macaddr,macaddr}
+ macaddr_gt | boolean | [0:1]={macaddr,macaddr}
+ macaddr_le | boolean | [0:1]={macaddr,macaddr}
+ macaddr_lt | boolean | [0:1]={macaddr,macaddr}
+ macaddr_ne | boolean | [0:1]={macaddr,macaddr}
+ nameeq | boolean | [0:1]={name,name}
+ namege | boolean | [0:1]={name,name}
+ namegt | boolean | [0:1]={name,name}
+ namele | boolean | [0:1]={name,name}
+ namelt | boolean | [0:1]={name,name}
+ namene | boolean | [0:1]={name,name}
+ network_eq | boolean | [0:1]={inet,inet}
+ network_ge | boolean | [0:1]={inet,inet}
+ network_gt | boolean | [0:1]={inet,inet}
+ network_le | boolean | [0:1]={inet,inet}
+ network_lt | boolean | [0:1]={inet,inet}
+ network_ne | boolean | [0:1]={inet,inet}
+ oideq | boolean | [0:1]={oid,oid}
+ oidge | boolean | [0:1]={oid,oid}
+ oidgt | boolean | [0:1]={oid,oid}
+ oidle | boolean | [0:1]={oid,oid}
+ oidlt | boolean | [0:1]={oid,oid}
+ oidne | boolean | [0:1]={oid,oid}
+ reltimeeq | boolean | [0:1]={reltime,reltime}
+ reltimege | boolean | [0:1]={reltime,reltime}
+ reltimegt | boolean | [0:1]={reltime,reltime}
+ reltimele | boolean | [0:1]={reltime,reltime}
+ reltimelt | boolean | [0:1]={reltime,reltime}
+ reltimene | boolean | [0:1]={reltime,reltime}
+ texteq | boolean | [0:1]={text,text}
+ textne | boolean | [0:1]={text,text}
+ tideq | boolean | [0:1]={tid,tid}
+ tidge | boolean | [0:1]={tid,tid}
+ tidgt | boolean | [0:1]={tid,tid}
+ tidle | boolean | [0:1]={tid,tid}
+ tidlt | boolean | [0:1]={tid,tid}
+ tidne | boolean | [0:1]={tid,tid}
+ time_eq | boolean | [0:1]={"time without time zone","time without time zone"}
+ time_ge | boolean | [0:1]={"time without time zone","time without time zone"}
+ time_gt | boolean | [0:1]={"time without time zone","time without time zone"}
+ time_le | boolean | [0:1]={"time without time zone","time without time zone"}
+ time_lt | boolean | [0:1]={"time without time zone","time without time zone"}
+ time_ne | boolean | [0:1]={"time without time zone","time without time zone"}
+ timestamp_eq | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamp_ge | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamp_gt | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamp_le | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamp_lt | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamp_ne | boolean | [0:1]={"timestamp without time zone","timestamp without time zone"}
+ timestamptz_eq | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timestamptz_ge | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timestamptz_gt | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timestamptz_le | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timestamptz_lt | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timestamptz_ne | boolean | [0:1]={"timestamp with time zone","timestamp with time zone"}
+ timetz_eq | boolean | [0:1]={"time with time zone","time with time zone"}
+ timetz_ge | boolean | [0:1]={"time with time zone","time with time zone"}
+ timetz_gt | boolean | [0:1]={"time with time zone","time with time zone"}
+ timetz_le | boolean | [0:1]={"time with time zone","time with time zone"}
+ timetz_lt | boolean | [0:1]={"time with time zone","time with time zone"}
+ timetz_ne | boolean | [0:1]={"time with time zone","time with time zone"}
+ tintervaleq | boolean | [0:1]={tinterval,tinterval}
+ tintervalge | boolean | [0:1]={tinterval,tinterval}
+ tintervalgt | boolean | [0:1]={tinterval,tinterval}
+ tintervalle | boolean | [0:1]={tinterval,tinterval}
+ tintervalleneq | boolean | [0:1]={tinterval,reltime}
+ tintervallenge | boolean | [0:1]={tinterval,reltime}
+ tintervallengt | boolean | [0:1]={tinterval,reltime}
+ tintervallenle | boolean | [0:1]={tinterval,reltime}
+ tintervallenlt | boolean | [0:1]={tinterval,reltime}
+ tintervallenne | boolean | [0:1]={tinterval,reltime}
+ tintervallt | boolean | [0:1]={tinterval,tinterval}
+ tintervalne | boolean | [0:1]={tinterval,tinterval}
+ uuid_eq | boolean | [0:1]={uuid,uuid}
+ uuid_ge | boolean | [0:1]={uuid,uuid}
+ uuid_gt | boolean | [0:1]={uuid,uuid}
+ uuid_le | boolean | [0:1]={uuid,uuid}
+ uuid_lt | boolean | [0:1]={uuid,uuid}
+ uuid_ne | boolean | [0:1]={uuid,uuid}
+ varbiteq | boolean | [0:1]={"bit varying","bit varying"}
+ varbitge | boolean | [0:1]={"bit varying","bit varying"}
+ varbitgt | boolean | [0:1]={"bit varying","bit varying"}
+ varbitle | boolean | [0:1]={"bit varying","bit varying"}
+ varbitlt | boolean | [0:1]={"bit varying","bit varying"}
+ varbitne | boolean | [0:1]={"bit varying","bit varying"}
+ xideq | boolean | [0:1]={xid,xid}
+(228 rows)
+
+--
+-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
+--
+CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 50';
+CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
+ CALLED ON NULL INPUT AS 'SELECT $1 = 50';
+CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
+ RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
+CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
+ STRICT AS 'SELECT $1 = 50';
+SELECT proname, proisstrict FROM pg_proc
+ WHERE oid in ('functext_F_1'::regproc,
+ 'functext_F_2'::regproc,
+ 'functext_F_3'::regproc,
+ 'functext_F_4'::regproc);
+ proname | proisstrict
+--------------+-------------
+ functext_f_1 | f
+ functext_f_2 | f
+ functext_f_3 | t
+ functext_f_4 | t
+(4 rows)
+
+ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_F_2(int) STRICT;
+ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT;
+SELECT proname, proisstrict FROM pg_proc
+ WHERE oid in ('functext_F_1'::regproc,
+ 'functext_F_2'::regproc,
+ 'functext_F_3'::regproc,
+ 'functext_F_4'::regproc);
+ proname | proisstrict
+--------------+-------------
+ functext_f_1 | f
+ functext_f_2 | t
+ functext_f_3 | f
+ functext_f_4 | t
+(4 rows)
+
+-- Cleanups
+DROP SCHEMA temp_func_test CASCADE;
+NOTICE: drop cascades to 19 other objects
+DETAIL: drop cascades to function functest_a_1(text,date)
+drop cascades to function functest_a_2(text[])
+drop cascades to function functest_a_3()
+drop cascades to function functest_b_1(integer)
+drop cascades to function functest_b_2(integer)
+drop cascades to function functest_b_3(integer)
+drop cascades to function functest_b_4(integer)
+drop cascades to function functext_c_1(integer)
+drop cascades to function functext_c_2(integer)
+drop cascades to function functext_c_3(integer)
+drop cascades to function functext_d_1(integer,integer)
+drop cascades to function functext_d_2(integer,integer)
+drop cascades to function functext_d_3(integer,integer)
+drop cascades to function functext_e_1(integer)
+drop cascades to function functext_e_2(integer)
+drop cascades to function functext_f_1(integer)
+drop cascades to function functext_f_2(integer)
+drop cascades to function functext_f_3(integer)
+drop cascades to function functext_f_4(integer)
+RESET search_path;
diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out
index 8c2cfc8499a..8278ede8bfb 100644
--- a/src/test/regress/expected/select_views.out
+++ b/src/test/regress/expected/select_views.out
@@ -1397,7 +1397,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum);
--
-- scenario: an external qualifier can be pushed-down by in-front-of the
--- views with "security_barrier" attribute
+-- views with "security_barrier" attribute, except for operators
+-- implemented with leakproof functions.
--
SELECT * FROM my_credit_card_usage_normal
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
@@ -1433,8 +1434,6 @@ SELECT * FROM my_credit_card_usage_secure
NOTICE: f_leak => 1111-2222-3333-4444
NOTICE: f_leak => 1111-2222-3333-4444
NOTICE: f_leak => 1111-2222-3333-4444
-NOTICE: f_leak => 1111-2222-3333-4444
-NOTICE: f_leak => 1111-2222-3333-4444
cid | name | tel | passwd | cnum | climit | ymd | usage
-----+---------------+------------------+-----------+---------------------+--------+------------+-------
101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90
@@ -1444,21 +1443,22 @@ NOTICE: f_leak => 1111-2222-3333-4444
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Subquery Scan on my_credit_card_usage_secure
- Filter: (f_leak(my_credit_card_usage_secure.cnum) AND (my_credit_card_usage_secure.ymd >= '10-01-2011'::date) AND (my_credit_card_usage_secure.ymd < '11-01-2011'::date))
- -> Hash Join
- Hash Cond: (r.cid = l.cid)
+ Filter: f_leak(my_credit_card_usage_secure.cnum)
+ -> Nested Loop
+ Join Filter: (l.cid = r.cid)
-> Seq Scan on credit_usage r
- -> Hash
+ Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
+ -> Materialize
-> Hash Join
Hash Cond: (r.cid = l.cid)
-> Seq Scan on credit_card r
-> Hash
-> Seq Scan on customer l
Filter: (name = ("current_user"())::text)
-(12 rows)
+(13 rows)
--
-- Test for the case when security_barrier gets changed between rewriter
diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out
index 0343f39645f..986b4b2d4a6 100644
--- a/src/test/regress/expected/select_views_1.out
+++ b/src/test/regress/expected/select_views_1.out
@@ -1397,7 +1397,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum);
--
-- scenario: an external qualifier can be pushed-down by in-front-of the
--- views with "security_barrier" attribute
+-- views with "security_barrier" attribute, except for operators
+-- implemented with leakproof functions.
--
SELECT * FROM my_credit_card_usage_normal
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
@@ -1433,8 +1434,6 @@ SELECT * FROM my_credit_card_usage_secure
NOTICE: f_leak => 1111-2222-3333-4444
NOTICE: f_leak => 1111-2222-3333-4444
NOTICE: f_leak => 1111-2222-3333-4444
-NOTICE: f_leak => 1111-2222-3333-4444
-NOTICE: f_leak => 1111-2222-3333-4444
cid | name | tel | passwd | cnum | climit | ymd | usage
-----+---------------+------------------+-----------+---------------------+--------+------------+-------
101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90
@@ -1444,21 +1443,22 @@ NOTICE: f_leak => 1111-2222-3333-4444
EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Subquery Scan on my_credit_card_usage_secure
- Filter: (f_leak(my_credit_card_usage_secure.cnum) AND (my_credit_card_usage_secure.ymd >= '10-01-2011'::date) AND (my_credit_card_usage_secure.ymd < '11-01-2011'::date))
- -> Hash Join
- Hash Cond: (r.cid = l.cid)
+ Filter: f_leak(my_credit_card_usage_secure.cnum)
+ -> Nested Loop
+ Join Filter: (l.cid = r.cid)
-> Seq Scan on credit_usage r
- -> Hash
+ Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
+ -> Materialize
-> Hash Join
Hash Cond: (r.cid = l.cid)
-> Seq Scan on credit_card r
-> Hash
-> Seq Scan on customer l
Filter: (name = ("current_user"())::text)
-(12 rows)
+(13 rows)
--
-- Test for the case when security_barrier gets changed between rewriter
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index d6105f85678..9749763ffd2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -40,6 +40,7 @@ test: create_function_1
test: create_type
test: create_table
test: create_function_2
+test: create_function_3
# ----------
# Load huge amounts of data
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 90726ce97b3..142fc9cf0d1 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -50,6 +50,7 @@ test: create_function_1
test: create_type
test: create_table
test: create_function_2
+test: create_function_3
test: copy
test: copyselect
test: create_misc
diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql
new file mode 100644
index 00000000000..1eed4e29797
--- /dev/null
+++ b/src/test/regress/sql/create_function_3.sql
@@ -0,0 +1,145 @@
+--
+-- CREATE FUNCTION
+--
+-- sanity check of pg_proc catalog to the given parameters
+--
+CREATE SCHEMA temp_func_test;
+
+SET search_path TO temp_func_test, public;
+
+--
+-- ARGUMENT and RETURN TYPES
+--
+CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
+CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
+ AS 'SELECT $1[0]::int';
+CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT false';
+SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
+ WHERE oid in ('functest_A_1'::regproc,
+ 'functest_A_2'::regproc,
+ 'functest_A_3'::regproc);
+
+--
+-- IMMUTABLE | STABLE | VOLATILE
+--
+CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 0';
+CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
+ IMMUTABLE AS 'SELECT $1 > 0';
+CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
+ STABLE AS 'SELECT $1 = 0';
+CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
+ VOLATILE AS 'SELECT $1 < 0';
+SELECT proname, provolatile FROM pg_proc
+ WHERE oid in ('functest_B_1'::regproc,
+ 'functest_B_2'::regproc,
+ 'functest_B_3'::regproc,
+ 'functest_B_4'::regproc);
+
+ALTER FUNCTION functest_B_2(int) VOLATILE;
+ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect
+SELECT proname, provolatile FROM pg_proc
+ WHERE oid in ('functest_B_1'::regproc,
+ 'functest_B_2'::regproc,
+ 'functest_B_3'::regproc,
+ 'functest_B_4'::regproc);
+
+--
+-- SECURITY DEFINER | INVOKER
+--
+CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 0';
+CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
+ SECURITY DEFINER AS 'SELECT $1 = 0';
+CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
+ SECURITY INVOKER AS 'SELECT $1 < 0';
+SELECT proname, prosecdef FROM pg_proc
+ WHERE oid in ('functext_C_1'::regproc,
+ 'functext_C_2'::regproc,
+ 'functext_C_3'::regproc);
+
+ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_C_2(int) SECURITY INVOKER;
+ALTER FUNCTION functext_C_3(int) SECURITY DEFINER;
+SELECT proname, prosecdef FROM pg_proc
+ WHERE oid in ('functext_C_1'::regproc,
+ 'functext_C_2'::regproc,
+ 'functext_C_3'::regproc);
+
+--
+-- COST
+--
+CREATE FUNCTION functext_D_1(int,int) RETURNS int LANGUAGE 'sql'
+ AS 'SELECT $1 + $2';
+CREATE FUNCTION functext_D_2(int,int) RETURNS int LANGUAGE 'internal'
+ AS 'int4pl';
+CREATE FUNCTION functext_D_3(int,int) RETURNS int LANGUAGE 'sql'
+ COST 500 AS 'SELECT $1 * $2';
+CREATE FUNCTION functext_D_4(int,int) RETURNS int LANGUAGE 'sql'
+ COST 0 AS 'SELECT $1 / $2'; -- Error
+SELECT proname, procost FROM pg_proc
+ WHERE oid in ('functext_D_1'::regproc,
+ 'functext_D_2'::regproc,
+ 'functext_D_3'::regproc);
+
+ALTER FUNCTION functext_D_1(int,int) STABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_D_2(int,int) COST 50;
+ALTER FUNCTION functext_D_3(int,int) COST 0.0001;
+SELECT proname, procost FROM pg_proc
+ WHERE oid in ('functext_D_1'::regproc,
+ 'functext_D_2'::regproc,
+ 'functext_D_3'::regproc);
+
+--
+-- LEAKPROOF
+--
+CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 100';
+CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
+ LEAKPROOF AS 'SELECT $1 > 100';
+SELECT proname, proleakproof FROM pg_proc
+ WHERE oid in ('functext_E_1'::regproc,
+ 'functext_E_2'::regproc);
+
+ALTER FUNCTION functext_E_1(int) LEAKPROOF;
+ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect
+SELECT proname, proleakproof FROM pg_proc
+ WHERE oid in ('functext_E_1'::regproc,
+ 'functext_E_2'::regproc);
+
+-- list of built-in leakproof functions
+SELECT proname, prorettype::regtype, proargtypes::regtype[]
+ FROM pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid
+ WHERE nspname = 'pg_catalog' AND proleakproof ORDER BY proname;
+
+--
+-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
+--
+CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
+ AS 'SELECT $1 > 50';
+CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
+ CALLED ON NULL INPUT AS 'SELECT $1 = 50';
+CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
+ RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
+CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
+ STRICT AS 'SELECT $1 = 50';
+SELECT proname, proisstrict FROM pg_proc
+ WHERE oid in ('functext_F_1'::regproc,
+ 'functext_F_2'::regproc,
+ 'functext_F_3'::regproc,
+ 'functext_F_4'::regproc);
+
+ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect
+ALTER FUNCTION functext_F_2(int) STRICT;
+ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT;
+SELECT proname, proisstrict FROM pg_proc
+ WHERE oid in ('functext_F_1'::regproc,
+ 'functext_F_2'::regproc,
+ 'functext_F_3'::regproc,
+ 'functext_F_4'::regproc);
+
+-- Cleanups
+DROP SCHEMA temp_func_test CASCADE;
+RESET search_path;
diff --git a/src/test/regress/sql/select_views.sql b/src/test/regress/sql/select_views.sql
index e626b9b94a8..e37fb7b3880 100644
--- a/src/test/regress/sql/select_views.sql
+++ b/src/test/regress/sql/select_views.sql
@@ -108,7 +108,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum);
--
-- scenario: an external qualifier can be pushed-down by in-front-of the
--- views with "security_barrier" attribute
+-- views with "security_barrier" attribute, except for operators
+-- implemented with leakproof functions.
--
SELECT * FROM my_credit_card_usage_normal
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-15 12:16:50 +0000