From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: quote_nullable alternative? |
Date: | 2007-11-09 07:14:30 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F42337E3C@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
pgsql-sql-owner(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2007-11-09 13:39:57 | design of tables for sparse data |
Previous Message | Philippe Lang | 2007-11-08 20:05:43 | quote_nullable alternative? |