plpgsql , dynamic queries

[Alex]

Hi,
I am having problems in forming a dynamic query that can be used in
Execute statements.
Problem:
I have database fields that are either INT or VARCHAR. Both can have NULL.

If I use variables that hold the db field's values ... how do I need to
quote them to pass them to the query string.

example:

_var1 := NULL;
_var2 := ''NULL'';

or
queryStr := ''WHERE name NOTNULL";
queryStr := ''WHERE name ='' || _var2/1

_var3 := ''somename'';
quereyStr := ''SET name='' || _var3;

I tried combinations but ending up with using 4 or 6 single quotes. Is
there an easy way to form such query strings ?

Any help would be appreciated.

Thanks

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Heath Tanner]

It would be easier to help you with the quoting issues if you provided
an example of the entire query you need to execute and/or the code
you're using to build the query.

Quoting in a function can get pretty hairy sometimes. You might find
this chart helpful, I certainly have:

http://www.postgresql.org/docs/7.3/s...l-porting.html (see
"19.11.1.1. Quote Me on That: Escaping Single Quotes")

I suspect this chart shouldn't be hidden in the "Porting from Oracle
PL/SQL" section of the docs.

-heath
On Wednesday, August 27, 2003, at 08:40 PM, Alex wrote:

If I use variables that hold the db field's values ... how do I need
to quote them to pass them to the query string.

example:

_var1 := NULL;
_var2 := ''NULL'';

or
queryStr := ''WHERE name NOTNULL";
queryStr := ''WHERE name ='' || _var2/1

_var3 := ''somename'';
quereyStr := ''SET name='' || _var3;

I tried combinations but ending up with using 4 or 6 single quotes. Is
there an easy way to form such query strings ?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org