Thread: test strange behavior

Hi list,

I've a function that test a boolean parm, as:
....
CASE $3
WHEN TRUE THEN
    blabla
WHEN FALSE THEN
    proutprout
ELSE
    gadugadu
END CASE;
....

AFAI understand, the ELSE case would normally be triggered with a NULL
but it isn't ('WHEN NULL THEN' also don't work).

Any clue?

JY
--
If men could get pregnant, abortion would be a sacrament.

"Jean-Yves F. Barbier" <12ukwn@gmail.com> writes:
> Hi list,
> I've a function that test a boolean parm, as:
> ....
> CASE $3
> WHEN TRUE THEN
>     blabla
> WHEN FALSE THEN
>     proutprout
> ELSE
>     gadugadu
> END CASE;
> ....

> AFAI understand, the ELSE case would normally be triggered with a NULL
> but it isn't ('WHEN NULL THEN' also don't work).

Works for me ... maybe you need to show a more complete example,
so that we can see where the problem really is?

            regards, tom lane

[ Please keep the list cc'd. ]

"Jean-Yves F. Barbier" <12ukwn@gmail.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

On Sun, 06 Nov 2011 18:50:29 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> [ Please keep the list cc'd. ]

Yeah I know: all other MLs I'm subscribed use the ML e-mail address
as the To: field, yours use the subscriber; so I have to hit
ctrl-shit-r instead of ctrl-l, and sometimes I forget.

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

Oops my bad, I read the doc diagonaly and missed this one.

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

Yep, its working correctly now, thanks Tom.

--
Out of the mouths of babes does often come cereal.