--

-- 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

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

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;

test: create_type

test: create_table

test: create_function_2

test: create_function_3

# ----------

# Load huge amounts of data

# ----------

# Another group of parallel tests

# ----------

test: create_aggregate create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists

test: create_aggregatecreate_function_3create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists

# ----------

# sanity_check does a vacuum, affecting the sort order of SELECT *

CREATEUSERregtest_unpriv_user;

CREATESCHEMAtemp_func_test;

GRANT ALLON SCHEMA temp_func_test TO public;

SET search_path TO temp_func_test, public;

WHEREoidin ('functext_E_1'::regproc,

'functext_E_2'::regproc)ORDER BY proname;

ALTERFUNCTION functext_E_2(int) NOT LEAKPROOF;-- remove leakproog attribute

SELECT proname, proleakproofFROM pg_proc

WHEREoidin ('functext_E_1'::regproc,

'functext_E_2'::regproc)ORDER BY proname;

ALTERFUNCTION functext_E_1(int) OWNER TO regtest_unpriv_user;

ALTERFUNCTION functext_E_2(int) OWNER TO regtest_unpriv_user;

SET SESSION AUTHORIZATION regtest_unpriv_user;

SET search_path TO temp_func_test, public;

ALTERFUNCTION functext_E_1(int) NOT LEAKPROOF;

ALTERFUNCTION functext_E_2(int) LEAKPROOF;

CREATEFUNCTIONfunctext_E_3(int) RETURNS bool LANGUAGE'sql'

LEAKPROOFAS'SELECT $1 < 200';-- failed

RESET SESSION AUTHORIZATION;

SELECT proname, prorettype::regtype, proargtypes::regtype[]

FROM pg_procJOIN pg_namespaceON pronamespace=pg_namespace.oid

DROPSCHEMA temp_func_test CASCADE;

DROPUSER regtest_unpriv_user;

RESET search_path;