From sgnerd@yahoo.com.sg Thu Feb  12 12:30:49 2004
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 CF574D1DA5F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Feb 2004 12:30:49 +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 74822-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Feb 2004 08:30:50 -0400 (AST)
Received: from smtp105.mail.sc5.yahoo.com (smtp105.mail.sc5.yahoo.com
	[66.163.169.225])
	by svr1.postgresql.org (Postfix) with SMTP id F1E2CD1DA57
	for <pgsql-sql@postgresql.org>; Thu, 12 Feb 2004 08:30:46 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp105.mail.sc5.yahoo.com with SMTP; 12 Feb 2004 12:30:48 -0000
Message-ID: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: How to avoid nulls while writing string for dynamic query
Date: Thu, 12 Feb 2004 18:01:12 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00BD_01C3F192.337AC930"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/111
X-Sequence-Number: 16802

This is a multi-part message in MIME format.

------=_NextPart_000_00BD_01C3F192.337AC930
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

Postgres 7.3.4 on RH Linux 7.2.

I wanted to write a dynamic query for insert statement.

create table test(c1 int, c2 varchar)

insert into test(c1, c2) values (1,'Hai1');
insert into test(c1, c2) values (NULL,'Hai2');


so I wrote a function called test_fn()

DECLARE
    sqlstr  VARCHAR(100);
     rec  RECORD;
BEGIN
     FOR rec IN SELECT * FROM test
     LOOP
          sqlstr :=3D 'insert into test(c1, c2) values (' ||rec.c1||','
                                                                    ||'\''|=
|rec.c2||'\')';
    RAISE NOTICE '%',sqlstr;
     execute sqlstr;
    END LOOP;
RETURN 'DONE';
END;

NOTICE:  insert into test(c1, c2) values (1,'Hai1')
NOTICE:  <NULL>

So i have created a null function.

  sqlstr :=3D 'insert into test(c1, c2) values (' ||ISNULL(rec.c1,'')||','
                                                                    ||'\''|=
|rec.c2||'\')';

Now I got results as
NOTICE:  insert into test(c1, c2) values (1,'Hai1')
NOTICE:  insert into test(c1, c2) values (,'Hai2')
WARNING:  Error occurred while executing PL/pgSQL function test_fn
WARNING:  line 11 at execute statement

ERROR:  parser: parse error at or near "," at character 34

The error is because of no value for column c1. If the column c1 is a strin=
g I might have replace it with empty string. I don't want to substitute wit=
h '0' which could work.

sqlstr :=3D 'insert into test(c1, c2) values (' ||ISNULL(rec.c1,'0')||','
                                                                    ||'\''|=
|rec.c2||'\')';

NOTICE:  insert into test(c1, c2) values (1,'Hai1')
NOTICE:  insert into test(c1, c2) values (0,'Hai2')

Total query runtime: 47 ms.
Data retrieval runtime: 0 ms.
1 rows retrieved.

How can I do that. Please advise me.

Thanks
Kumar





------=_NextPart_000_00BD_01C3F192.337AC930
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Postgres 7.3.4 on RH Linux 7.2.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I wanted to write a dynamic query for insert=20
statement.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>create table test(c1 int, c2 varchar)</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>insert into test(c1, c2) values (1,'Hai1');<BR>insert i=
nto=20
test(c1, c2) values (NULL,'Hai2');<BR></FONT><FONT size=3D2></FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>so I wrote a function called test_fn()</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>DECLARE<BR>&nbsp;&nbsp;&nbsp;=20
sqlstr&nbsp;&nbsp;VARCHAR(100);<BR>&nbsp;&nbsp;&nbsp;&nbsp;=20
rec&nbsp;&nbsp;RECORD;<BR>BEGIN<BR>&nbsp;&nbsp;&nbsp; &nbsp;FOR rec=20
IN&nbsp;SELECT&nbsp;* FROM&nbsp;test<BR>&nbsp;&nbsp;&nbsp;=20
&nbsp;LOOP<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;sqlstr&nbsp=
;:=3D=20
'insert into test(c1, c2) values=20
('&nbsp;||rec.c1||','<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
||'\''||rec.c2||'\')';<BR>&nbsp;&nbsp;&nbsp;&nbsp;RAISE NOTICE=20
'%',sqlstr;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;execute=20
sqlstr;<BR>&nbsp;&nbsp;&nbsp; END LOOP;<BR>RETURN 'DONE';<BR>END;</FONT></D=
IV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>NOTICE:&nbsp; insert into test(c1, c2) values=20
(1,'Hai1')<BR>NOTICE:&nbsp; &lt;NULL&gt;</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>So i have created a null function.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;sqlstr&nbsp;:=3D 'insert into test(c1, c2) =
values=20
('&nbsp;||ISNULL(rec.c1,'')||','<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ||'\''||rec.c2||'\')';</FONT><BR></DI=
V>
<DIV><FONT size=3D2>Now I got results as</FONT></DIV>
<DIV><FONT size=3D2>NOTICE:&nbsp; insert into test(c1, c2) values=20
(1,'Hai1')<BR>NOTICE:&nbsp; insert into test(c1, c2) values=20
(,'Hai2')<BR>WARNING:&nbsp; Error occurred while executing PL/pgSQL functio=
n=20
test_fn<BR>WARNING:&nbsp; line 11 at execute statement</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>ERROR:&nbsp; parser: parse error at or near "," at char=
acter=20
34</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>The error is because of no value for column c1. If the =
column=20
c1 is a string I might have replace it with empty string. I don't want to=
=20
substitute with '0' which could work.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>sqlstr&nbsp;:=3D 'insert into test(c1, c2) values=20
('&nbsp;||ISNULL(rec.c1,'0')||','<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ||'\''||rec.c2||'\')';</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>NOTICE:&nbsp; insert into test(c1, c2) values=20
(1,'Hai1')<BR>NOTICE:&nbsp; insert into test(c1, c2) values=20
(0,'Hai2')</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Total query runtime: 47 ms.<BR>Data retrieval runtime: =
0=20
ms.<BR>1 rows retrieved.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>How can I do that. Please advise me.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>&nbsp;</DIV>
<DIV><BR></DIV></FONT>
<P></P></BODY></HTML>

------=_NextPart_000_00BD_01C3F192.337AC930--




From jasiek@klaster.net Thu Feb  12 13:02:33 2004
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 055BFD1C9BE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Feb 2004 13:02:33 +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 86902-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Feb 2004 09:02:31 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id AE4B8D1B911
	for <pgsql-sql@postgresql.org>; Thu, 12 Feb 2004 09:02:26 -0400 (AST)
Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 7BCFC2B3D0; Thu, 12 Feb 2004 13:40:38 +0100 (CET)
Message-ID: <402B74F4.7070604@klaster.net>
Date: Thu, 12 Feb 2004 13:43:32 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
	rv:1.6b) Gecko/20031208
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>
In-Reply-To: <00c001c3f164$1db30140$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/112
X-Sequence-Number: 16803

Dnia 2004-02-12 13:31, Użytkownik Kumar napisał:
> The error is because of no value for column c1. If the column c1 is a 
> string I might have replace it with empty string. I don't want to 
> substitute with '0' which could work.
>  
> sqlstr := 'insert into test(c1, c2) values (' ||ISNULL(rec.c1,'0')||','
>                                                                     
> ||'\''||rec.c2||'\')';

Substitute it with NULL value:
sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.c1,'NULL') 
||...

Regards,
Tomasz Myrta



From sgnerd@yahoo.com.sg Fri Feb  13 04:53:47 2004
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 B0DEBD1C515
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 04:53:47 +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 41650-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 00:53:24 -0400 (AST)
Received: from smtp017.mail.yahoo.com (smtp017.mail.yahoo.com
	[216.136.174.114])
	by svr1.postgresql.org (Postfix) with SMTP id C0C17D1D69B
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 00:53:18 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp017.mail.yahoo.com with SMTP; 13 Feb 2004 04:53:17 -0000
Message-ID: <006701c3f1ed$6610dc30$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
	<402B74F4.7070604@klaster.net>
Subject: Re: How to avoid nulls while writing string for dynamic query
Date: Fri, 13 Feb 2004 10:23:58 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/132
X-Sequence-Number: 16823

I am having problem there. see what happens

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

WARNING:  Error occurred while executing PL/pgSQL function test_fn
WARNING:  line 8 at assignment
ERROR:  pg_atoi: error in "NULL": can't parse "NULL"

Dont forgot that c1 is int.

when i have like this
sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,NULL)||','
        ||'\''||rec.c2||'\')';

NOTICE:  <NULL>
WARNING:  Error occurred while executing PL/pgSQL function test_fn
WARNING:  line 11 at execute statement
ERROR:  cannot EXECUTE NULL query

That is the problem i am facing. Please shed some light.

Thanks
Kumar

----- Original Message ----- 
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Thursday, February 12, 2004 6:13 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query


> Dnia 2004-02-12 13:31, Użytkownik Kumar napisał:
> > The error is because of no value for column c1. If the column c1 is a
> > string I might have replace it with empty string. I don't want to
> > substitute with '0' which could work.
> >
> > sqlstr := 'insert into test(c1, c2) values (' ||ISNULL(rec.c1,'0')||','
> >
> > ||'\''||rec.c2||'\')';
>
> Substitute it with NULL value:
> sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.c1,'NULL')
> ||...
>
> Regards,
> Tomasz Myrta




From sgnerd@yahoo.com.sg Fri Feb  13 05:41:28 2004
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 6A13DD1B4B5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 05:41:28 +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 51807-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 01:41:22 -0400 (AST)
Received: from smtp105.mail.sc5.yahoo.com (smtp105.mail.sc5.yahoo.com
	[66.163.169.225])
	by svr1.postgresql.org (Postfix) with SMTP id 19A3ED1B4C9
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 01:41:21 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp105.mail.sc5.yahoo.com with SMTP; 13 Feb 2004 05:41:20 -0000
Message-ID: <009901c3f1f4$1c519a10$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Kumar" <sgnerd@yahoo.com.sg>,
	"Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
	<402B74F4.7070604@klaster.net>
	<006701c3f1ed$6610dc30$7502a8c0@hdsc.com>
Subject: Re: How to avoid nulls while writing string for dynamic query
Date: Fri, 13 Feb 2004 11:11:49 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/134
X-Sequence-Number: 16825

Dear all,
I solved it using ISNULL function.
sqlstr := 'insert into test(c1, c2) values ('||ISNULL(rec.c1,'NULL')||','
>         ||'\''||rec.c2||'\')';
Thanks
kumar
----- Original Message ----- 
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Friday, February 13, 2004 10:23 AM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query


> I am having problem there. see what happens
>
> sqlstr := 'insert into test(c1, c2) values
('||COALESCE(rec.c1,'NULL')||','
>         ||'\''||rec.c2||'\')';
>
> WARNING:  Error occurred while executing PL/pgSQL function test_fn
> WARNING:  line 8 at assignment
> ERROR:  pg_atoi: error in "NULL": can't parse "NULL"
>
> Dont forgot that c1 is int.
>
> when i have like this
> sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,NULL)||','
>         ||'\''||rec.c2||'\')';
>
> NOTICE:  <NULL>
> WARNING:  Error occurred while executing PL/pgSQL function test_fn
> WARNING:  line 11 at execute statement
> ERROR:  cannot EXECUTE NULL query
>
> That is the problem i am facing. Please shed some light.
>
> Thanks
> Kumar
>
> ----- Original Message ----- 
> From: "Tomasz Myrta" <jasiek@klaster.net>
> To: "Kumar" <sgnerd@yahoo.com.sg>
> Cc: "psql" <pgsql-sql@postgresql.org>
> Sent: Thursday, February 12, 2004 6:13 PM
> Subject: Re: [SQL] How to avoid nulls while writing string for dynamic
query
>
>
> > Dnia 2004-02-12 13:31, Użytkownik Kumar napisał:
> > > The error is because of no value for column c1. If the column c1 is a
> > > string I might have replace it with empty string. I don't want to
> > > substitute with '0' which could work.
> > >
> > > sqlstr := 'insert into test(c1, c2) values ('
||ISNULL(rec.c1,'0')||','
> > >
> > > ||'\''||rec.c2||'\')';
> >
> > Substitute it with NULL value:
> > sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.c1,'NULL')
> > ||...
> >
> > Regards,
> > Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings




From jasiek@klaster.net Fri Feb  13 06:30:40 2004
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 7C1BFD1D3A4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 06:30:40 +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 61184-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 02:30:39 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id A9BB1D1B4B5
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 02:30:37 -0400 (AST)
Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 1A34A2B3CB; Fri, 13 Feb 2004 07:30:32 +0100 (CET)
Message-ID: <402C6FB8.8000606@klaster.net>
Date: Fri, 13 Feb 2004 07:33:28 +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>
In-Reply-To: <006701c3f1ed$6610dc30$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/136
X-Sequence-Number: 16827

Dnia 2004-02-13 05:53, Użytkownik Kumar napisał:

> I am having problem there. see what happens
> 
> sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,'NULL')||','
>         ||'\''||rec.c2||'\')';

You are preparing a string, so make sure you have strings everywhere:
sqlstr := 'insert into test(c1, c2) values 
('||COALESCE(rec.c1::text,'NULL')||','||'\''||rec.c2||'\')';

Regards,
Tomasz Myrta




From sgnerd@yahoo.com.sg Fri Feb  13 07:12:47 2004
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 15958D1D8F3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 07:12:47 +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 70326-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 03:12:46 -0400 (AST)
Received: from smtp104.mail.sc5.yahoo.com (smtp104.mail.sc5.yahoo.com
	[66.163.169.223])
	by svr1.postgresql.org (Postfix) with SMTP id 9C013D1D8AE
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 03:12:44 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp104.mail.sc5.yahoo.com with SMTP; 13 Feb 2004 07:12:44 -0000
Message-ID: <00c801c3f200$e117eff0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
	<402B74F4.7070604@klaster.net>
	<006701c3f1ed$6610dc30$7502a8c0@hdsc.com>
	<402C6FB8.8000606@klaster.net>
Subject: Re: How to avoid nulls while writing string for dynamic query
Date: Fri, 13 Feb 2004 12:43:26 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/138
X-Sequence-Number: 16829

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'

I think using 'CASE' this could be solved. But instead is there any other
simple way to do it.

Thanks a lot Mr. Tomasz Myrta
Kumar


----- Original Message ----- 
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Friday, February 13, 2004 12:03 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query


> Dnia 2004-02-13 05:53, Użytkownik Kumar napisał:
>
> > I am having problem there. see what happens
> >
> > sqlstr := 'insert into test(c1, c2) values
('||COALESCE(rec.c1,'NULL')||','
> >         ||'\''||rec.c2||'\')';
>
> You are preparing a string, so make sure you have strings everywhere:
> sqlstr := 'insert into test(c1, c2) values
> ('||COALESCE(rec.c1::text,'NULL')||','||'\''||rec.c2||'\')';
>
> Regards,
> Tomasz Myrta




From jasiek@klaster.net Fri Feb  13 08:04:26 2004
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




From sgnerd@yahoo.com.sg Fri Feb  13 09:13:46 2004
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 0238CD1D26D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 09:13:46 +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 98237-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 05:13:44 -0400 (AST)
Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com
 [216.136.173.59])
	by svr1.postgresql.org (Postfix) with SMTP id 2165AD1D141
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 05:13:42 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp015.mail.yahoo.com with SMTP; 13 Feb 2004 09:13:42 -0000
Message-ID: <011101c3f211$c78d7c10$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
	<402B74F4.7070604@klaster.net>
	<006701c3f1ed$6610dc30$7502a8c0@hdsc.com>
	<402C6FB8.8000606@klaster.net>
	<00c801c3f200$e117eff0$7502a8c0@hdsc.com>
	<402C85B3.2020204@klaster.net>
Subject: Re: How to avoid nulls while writing string for dynamic query
Date: Fri, 13 Feb 2004 14:44:24 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/141
X-Sequence-Number: 16832

Thanks Tomasz Myrta. It is wonderful. I am still amazed from where you guys
knowing the options like quote_literal, etc.

Kumar

----- Original Message ----- 
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Friday, February 13, 2004 1:37 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query


> 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




From jasiek@klaster.net Fri Feb  13 09:25:40 2004
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 AF917D1CA90
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 09:25:40 +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 03823-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 Feb 2004 05:25:39 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 1C27BD1C515
	for <pgsql-sql@postgresql.org>; Fri, 13 Feb 2004 05:25:36 -0400 (AST)
Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 7570D2B3CD; Fri, 13 Feb 2004 10:25:24 +0100 (CET)
Message-ID: <402C98B4.9000201@klaster.net>
Date: Fri, 13 Feb 2004 10:28:20 +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>
	<402C85B3.2020204@klaster.net>
	<011101c3f211$c78d7c10$7502a8c0@hdsc.com>
In-Reply-To: <011101c3f211$c78d7c10$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/142
X-Sequence-Number: 16833

Dnia 2004-02-13 10:14, Użytkownik Kumar napisał:
> Thanks Tomasz Myrta. It is wonderful. I am still amazed from where you guys
> knowing the options like quote_literal, etc.
> 
> Kumar

Just read the manual ;-)
6.4. String Functions and Operators

Tomasz