Skip site navigation (1) Skip section navigation (2)

How to avoid nulls while writing string for dynamic query

From: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>
To: "psql" <pgsql-sql(at)postgresql(dot)org>
Subject: How to avoid nulls while writing string for dynamic query
Date: 2004-02-12 12:31:12
Message-ID: 00c001c3f164$1db30140$7502a8c0@hdsc.com (view raw or flat)
Thread:
Lists: pgsql-sql
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




Responses

pgsql-sql by date

Next:From: Tomasz MyrtaDate: 2004-02-12 12:43:32
Subject: Re: How to avoid nulls while writing string for dynamic query
Previous:From: Sumita Biswas (sbiswas)Date: 2004-02-12 11:40:38
Subject: Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group