Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] quote_literal with NULL

From: "Brendan Jurd" <direvus(at)gmail(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Re: [HACKERS] quote_literal with NULL
Date: 2007-10-11 16:11:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
Hi patchers,

Per discussion on -hackers, I've implemented a new internal function
quote_nullable, as an alternative to quote_literal.  The difference is
that quote_nullable returns the text value 'NULL' on NULL input, which
is suitable for insertion into an SQL statement.

The idea is that when you're writing a plpgsql function with dynamic
queries, you can use quote_nullable for values which are
possibly-null.  You're still responsible for handling NULLs sensibly
within your query, but at least you get a syntactically valid SQL

I've included doc updates but no new regression tests.  I did not add
tests because there are currently no tests for quote_literal and when
I recently suggested addition of tests for quote_ident [1] they were
rejected.  I still don't fully understand the criteria for inclusion
of regression tests, but this is a similar situation, so I'm following
the same guidance.

Patch compiles cleanly and passes make check on x86 gentoo.

Thanks for your time,


On 10/11/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Well, it's clearly useful in INSERT and UPDATE.  For WHERE cases, you
> might or might not be able to use it, but I note that quote_nullable()
> would work much more like what happens if you use a parameter symbol
> and then bind NULL as the actual parameter value ...
> In hindsight we should probably have done quote_literal the way the OP
> suggests, but I concur that it's too late to change it.  An additional
> function seems a reasonable compromise.

Attachment: quote-nullable_0.diff
Description: text/plain (6.3 KB)

In response to


pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-10-11 16:46:12
Subject: Re: Skytools committed without hackers discussion/review
Previous:From: Alexey KlyukinDate: 2007-10-11 16:10:18
Subject: Re: Some questions about mammoth replication

pgsql-patches by date

Next:From: Tom LaneDate: 2007-10-11 18:20:26
Subject: Re: Packed varlena tuptoaster.c oops
Previous:From: Gregory StarkDate: 2007-10-11 14:51:22
Subject: Packed varlena tuptoaster.c oops

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group