Обсуждение: quote_nullable alternative?

Hi,

There has been a recent discussion on the hacker mailing-list recently,
regarding a new patch for postgresql, that returns 'NULL' instead of
NULL when a NULL value is passed as a parameter.

It's exactly what I need on my 8.2.5 server.

Is there an alternative to patching the server in order to get that kind
of feature, for example with plpgsql code?

Because of that bug:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

... I'm obliged to call EXECUTE at every temporary table call, and
constructing the query string where parameters can be NULL is really a
nightmare...

Thanks

Philippe Lang

pgsql-sql-owner@postgresql.org wrote:
> Hi,
>
> There has been a recent discussion on the hacker mailing-list
> recently, regarding a new patch for postgresql, that returns 'NULL'
> instead of NULL when a NULL value is passed as a parameter.
>
> It's exactly what I need on my 8.2.5 server.
>
> Is there an alternative to patching the server in order to get that
> kind of feature, for example with plpgsql code?
>
> Because of that bug:
>
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
>
> ... I'm obliged to call EXECUTE at every temporary table call, and
> constructing the query string where parameters can be NULL is really
> a nightmare...
>
> Thanks

Hi,

It might not be the most elegant solution, but I ended up writing these
functions to solve my problem:


CREATE OR REPLACE FUNCTION nullable_param(param text) RETURNS text AS
$$ BEGIN   IF param IS NULL THEN     RETURN 'NULL';   ELSE     RETURN '''' || param || '''';   END IF; END
$$ LANGUAGE 'plpgsql' VOLATILE;
 CREATE OR REPLACE FUNCTION nullable_param(param varchar) RETURNS varchar AS
$$ BEGIN   IF param IS NULL THEN     RETURN 'NULL';   ELSE     RETURN '''' || param || '''';   END IF; END
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param decimal) RETURNS varchar AS
$$ BEGIN   IF param IS NULL THEN     RETURN 'NULL';   ELSE     RETURN CAST(param AS varchar);   END IF; END
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param int) RETURNS varchar AS
$$ BEGIN   IF param IS NULL THEN     RETURN 'NULL';   ELSE     RETURN CAST(param AS varchar);   END IF; END
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param date) RETURNS varchar AS
$$ BEGIN   IF param IS NULL THEN     RETURN 'NULL';   ELSE     RETURN '''' || param || '''';   END IF; END
$$ LANGUAGE 'plpgsql' VOLATILE;



Regards,

Philippe