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
Message-ID: <00c001c3f164$1db30140$7502a8c0@hdsc.com> (raw)
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 := '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 := '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 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||'\')';
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgnerd@yahoo.com.sg
Subject: Re: How to avoid nulls while writing string for dynamic query
In-Reply-To: <00c001c3f164$1db30140$7502a8c0@hdsc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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