Thread: Transform_Null_Equals does not work in Functions

Hy Group,

we use:
 PostgreSQL 9.0alpha4, compiled by Visual C++ build 1400, 32-bit

and i tried to set Transform_null_equals in a Trigger to avoid a complex If Statement with many Coalesce, but it didnt work. You can try it easily with that example:


CREATE OR REPLACE FUNCTION show_transform_problem(with_transform BOOL) RETURNS BOOL AS $$
DECLARE result BOOL;
BEGIN
 IF with_transform THEN
     SET transform_null_equals TO ON;
 END IF;
 RESULT:=NULL=1;
 SET transform_null_equals TO OFF;
 RETURN result;
END $$ LANGUAGE plpgsql;


SUNFLOWER=# SELECT show_transform_problem(false);
 show_transform_problem
------------------------

(1 row)


SUNFLOWER=# SELECT show_transform_problem(true);
 show_transform_problem
------------------------

(1 row)


SUNFLOWER=# SET transform_null_equals TO ON;
SET
SUNFLOWER=# SELECT null=1;
 ?column?
----------
 f
(1 row)

Daniel Schuchardt <d.schuchardt@prodat-sql.de> writes:
> CREATE OR REPLACE FUNCTION show_transform_problem(with_transform BOOL)
> RETURNS BOOL AS $$
> DECLARE result BOOL;
> BEGIN
>   IF with_transform THEN
>       SET transform_null_equals TO ON;
>   END IF;
>   RESULT:=NULL=1;
>   SET transform_null_equals TO OFF;
>   RETURN result;
> END $$ LANGUAGE plpgsql;

That isn't going to work unless you EXECUTE the target statement, so
that it's re-parsed from scratch every time.

It's highly unlikely that transform_null_equals will solve your problem
anyway.  Find a more standards-compliant way.

            regards, tom lane