Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id AC9BD9F925E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Nov 2007 03:14:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 29805-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Nov 2007 03:14:34 -0400 (AST)
Received: from mail.attiksystem.ch (f29.attiksystem.ch [212.147.59.29])
	by postgresql.org (Postfix) with ESMTP id 98FCC9F924B
	for <pgsql-sql@postgresql.org>; Fri,  9 Nov 2007 03:14:33 -0400 (AST)
Received: from poweredge.attiksystem.ch (poweredge.attiksystem.ch [10.0.0.29])
	by mail.attiksystem.ch (8.12.11/8.12.11) with ESMTP id lA97EU5L077651
	for <pgsql-sql@postgresql.org>; Fri, 9 Nov 2007 08:14:31 +0100 (CET)
	(envelope-from philippe.lang@attiksystem.ch)
Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: quote_nullable alternative?
Date: Fri, 9 Nov 2007 08:14:30 +0100
X-MimeOLE: Produced By Microsoft Exchange V6.0.6619.12
Message-ID: <6C0CF58A187DA5479245E0830AF84F42337E3C@poweredge.attiksystem.ch>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] quote_nullable alternative?
thread-index: AcgiQr9O7YsEW4KvSbuu4XWrZoij2wAXRM6w
References: <6C0CF58A187DA5479245E0830AF84F4218CF6B@poweredge.attiksystem.ch>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>
X-Scanned-By: MIMEDefang 2.49 on 10.0.0.111
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/31
X-Sequence-Number: 29559

pgsql-sql-owner@postgresql.org wrote:
> Hi,
>=20
> 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. =20
>=20
> It's exactly what I need on my 8.2.5 server.
>=20
> Is there an alternative to patching the server in order to get that
> kind of feature, for example with plpgsql code?=20
>=20
> Because of that bug:
>=20
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
>=20
> ... 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... =20
>=20
> 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