X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 36448D1B4E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 08:04:26 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 78587-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 04:04:25 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 59856D1B4DA
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 04:04:22 -0400 (AST)
Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id C9DE32B3CB; Fri, 13 Feb 2004 09:04:19 +0100 (CET)
Message-ID: <402C85B3.2020204@klaster.net>
Date: Fri, 13 Feb 2004 09:07:15 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.6) Gecko/20040113
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: How to avoid nulls while writing string for dynamic query
References: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
	<402B74F4.7070604@klaster.net>
	<006701c3f1ed$6610dc30$7502a8c0@hdsc.com>
	<402C6FB8.8000606@klaster.net>
	<00c801c3f200$e117eff0$7502a8c0@hdsc.com>
In-Reply-To: <00c801c3f200$e117eff0$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/140
X-Sequence-Number: 16831

Dnia 2004-02-13 08:13, Użytkownik Kumar napisał:
> oh, ok understood.
> What will happen for a timestamp field. Let us say c1 is a timestamp column.
> 
> sqlstr := 'insert into test(c1, c2) values
> ('||'\''||COALESCE(rec.c1,'NULL')||'\','
> 
>>>        ||'\''||rec.c2||'\')';
> 
> 
> If this case the query will be
> insert into test(c1,c2) values ('2004-02-13', 'Hai')
> 
> If there is a null value encountered i will return an error for the
> following query
> insert into test(c1,c2) values ('NULL', 'Hai')
> ERROR:  Bad timestamp external representation 'NULL'
It's because you can't use quotes with null. Valid query is:
insert into test(c1,c2) values (NULL, 'Hai');

Your dynamic query will then look like:

sqlstr := 'insert into test(c1, c2) values (' ||COALESCE('\'' || rec.c1 
|| '\'','NULL') ...

or more elegant:

sqlstr := 'insert into test(c1, c2) values (' 
||COALESCE(quote_literal(rec.c1),'NULL') ...

Regards,
Tomasz Myrta