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
|
- Re: [SQL] How to avoid nulls while writing string for dynamic... Kumar
- Re: [SQL] How to avoid nulls while writing string for dy... Tomasz Myrta
- Re: [SQL] How to avoid nulls while writing string fo... Kumar
- Re: [SQL] How to avoid nulls while writing strin... Kumar
- Re: [SQL] How to avoid nulls while writing strin... Tomasz Myrta
- Re: [SQL] How to avoid nulls while writing s... Kumar
- Re: [SQL] How to avoid nulls while writ... Tomasz Myrta
- Re: [SQL] How to avoid nulls while ... Kumar
- Re: [SQL] How to avoid nulls wh... Tomasz Myrta