-- -- CREATE FUNCTION -- -- sanity check of pg_proc catalog to the given parameters -- CREATE USER regtest_unpriv_user; CREATE SCHEMA temp_func_test; GRANT ALL ON SCHEMA temp_func_test TO public; 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) ORDER BY proname; 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) ORDER BY proname; 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) ORDER BY proname; 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) ORDER BY proname; 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) ORDER BY proname; proname | prosecdef --------------+----------- functext_c_1 | f functext_c_2 | f functext_c_3 | t (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) ORDER BY proname; 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) ORDER BY proname; proname | proleakproof --------------+-------------- functext_e_1 | t functext_e_2 | t (2 rows) ALTER FUNCTION functext_E_2(int) NOT LEAKPROOF; -- remove leakproog attribute SELECT proname, proleakproof FROM pg_proc WHERE oid in ('functext_E_1'::regproc, 'functext_E_2'::regproc) ORDER BY proname; proname | proleakproof --------------+-------------- functext_e_1 | t functext_e_2 | f (2 rows) -- it takes superuser privilege to turn on leakproof, but not for turn off ALTER FUNCTION functext_E_1(int) OWNER TO regtest_unpriv_user; ALTER FUNCTION functext_E_2(int) OWNER TO regtest_unpriv_user; SET SESSION AUTHORIZATION regtest_unpriv_user; SET search_path TO temp_func_test, public; ALTER FUNCTION functext_E_1(int) NOT LEAKPROOF; ALTER FUNCTION functext_E_2(int) LEAKPROOF; ERROR: only superuser can define a leakproof function CREATE FUNCTION functext_E_3(int) RETURNS bool LANGUAGE 'sql' LEAKPROOF AS 'SELECT $1 < 200'; -- failed ERROR: only superuser can define a leakproof function RESET SESSION AUTHORIZATION; -- 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) ORDER BY proname; 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) ORDER BY proname; 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 16 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_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) DROP USER regtest_unpriv_user; RESET search_path;