Thread: plpgsql , dynamic queries

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

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/static/plpgsql-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 ?