Re: test strange behavior

[ Please keep the list cc'd. ]

"Jean-Yves F. Barbier" <12ukwn(at)gmail(dot)com> writes:
> CREATE OR REPLACE FUNCTION e_sch_fun(TEXT, -- 1: Function(s) owner
> TEXT, -- 2: Schema
> BOOLEAN) -- 3: TRUE=ERP, FALSE=Other, NULL=ALL
> RETURNS SETOF RECORD AS $$
> DECLARE
> qry TEXT;
> BEGIN
> qry := 'SELECT proname, pronargs, format_type(proargtypes[0], NULL) AS arg0,
> format_type(proargtypes[1], NULL) AS arg1,
> format_type(proargtypes[2], NULL) AS arg2,
> format_type(proargtypes[3], NULL) AS arg3,
> format_type(proargtypes[4], NULL) AS arg4,
> format_type(proargtypes[5], NULL) AS arg5,
> format_type(proargtypes[6], NULL) AS arg6,
> format_type(proargtypes[7], NULL) AS arg7,
> format_type(proargtypes[8], NULL) AS arg8,
> format_type(proargtypes[9], NULL) AS arg9
> FROM pg_proc
> JOIN pg_namespace N ON pronamespace=N.oid ';

> CASE $3
> WHEN TRUE THEN
> qry := qry || 'WHERE proowner = (SELECT e_usr_oid('''||$1||'''))
> AND proname LIKE ''e\_%''
> AND pronamespace = (SELECT schoid FROM
> (SELECT * FROM e_sch()
> AS y(schoid OID, schname NAME)
> WHERE schname = '''||$2||''')
> AS z(schoid, schname));';

> WHEN FALSE THEN
> qry := qry || 'WHERE proowner = (SELECT e_usr_oid('''||$1||'''))
> AND proname NOT LIKE ''e\_%''
> AND pronamespace = (SELECT schoid FROM
> (SELECT * FROM e_sch()
> AS y(schoid OID, schname NAME)
> WHERE schname = '''||$2||''')
> AS z(schoid, schname));';
> -- NB: Here, ELSE (or WHEN NULL THEN) don't work and I don't know why??!
> ELSE
> qry := qry || 'WHERE proowner = (SELECT e_usr_oid('''||$1||'''))
> AND pronamespace = (SELECT schoid FROM
> (SELECT * FROM e_sch()
> AS y(schoid OID, schname NAME)
> WHERE schname = '''||$2||''')
> AS z(schoid, schname));';
> END CASE;
> RETURN QUERY EXECUTE qry;
> END;
> $$ LANGUAGE plpgsql STRICT SECURITY DEFINER STABLE;

The reason this function doesn't do anything for null inputs is you've
declared it STRICT. So it doesn't get called at all, and you get no
rows out:

> =# SELECT * FROM e_sch_fun('dbowner', 'public', NULL)
> AS z(funname NAME, funargnb SMALLINT,
> arg0 text, arg1 text, arg2 text, arg3 text, arg4 text,
> arg5 text, arg6 text, arg7 text, arg8 text, arg9 text);
> funname | funargnb | arg0 | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 | arg7 | arg8 | arg9
> ---------+----------+------+------+------+------+------+------+------+------+------+------
> (0 ligne)

> Apparently only the 1st part of qry is build in the NULL case.

If that were the correct explanation, you'd have been getting the whole
table back, not none of it.

regards, tom lane