pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

quote_nullable alternative?
2+ messages / 1 participants
[nested] [flat]

* quote_nullable alternative?
@ 2007-11-08 20:05  Philippe Lang <philippe.lang@attiksystem.ch>
  0 siblings, 1 reply; 2+ messages in thread

From: Philippe Lang @ 2007-11-08 20:05 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: quote_nullable alternative?
@ 2007-11-09 07:14  Philippe Lang <philippe.lang@attiksystem.ch>
  parent: Philippe Lang <philippe.lang@attiksystem.ch>
  0 siblings, 0 replies; 2+ messages in thread

From: Philippe Lang @ 2007-11-09 07:14 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-11-09 07:14 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-08 20:05 quote_nullable alternative? Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-09 07:14 ` Philippe Lang <philippe.lang@attiksystem.ch>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox