Re: [PATCHES] [HACKERS] quote_literal with NULL

[Simon Riggs]

On Fri, 2007-10-12 at 02:11 +1000, Brendan Jurd wrote:

 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.

Patch looks fine.

 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
 statement.
 
 I've included doc updates but no new regression tests. 

I think you should add some examples to show how we would handle an
INSERT or an UPDATE SET with quite_nullable() and a SELECT WHERE clause
with quote_literal. The difference is a subtle one, which is why nobody
mentioned it before, so it needs some better docs too.

A cross-ref to the functions page would help also.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PATCHES] [HACKERS] quote_literal with NULL

[Brendan Jurd]

On 10/12/07, Simon Riggs [EMAIL PROTECTED] wrote:
 I think you should add some examples to show how we would handle an
 INSERT or an UPDATE SET with quite_nullable() and a SELECT WHERE clause
 with quote_literal. The difference is a subtle one, which is why nobody
 mentioned it before, so it needs some better docs too.

 A cross-ref to the functions page would help also.

Thanks for your comments Simon.  I agree about the doco, and will send
in an updated patch soon.

Looking at the patch again, I was thinking; is there actually any
point having separate underlying C functions for quote_nullable and
quote_literal?  If I merged the functions together, and pointed both
pg_proc entries at the one combined function wouldn't it have the same
effect?

Perhaps having the separate function makes the intent of the code more
obvious, but looking at the patch with fresh eyes I'm thinking it's
mostly a waste of space.

Cheers,
BJ

---(end of broadcast)---
TIP 6: explain analyze is your friend