plpgsql & string building

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

plpgsql & string building

Wells Oliver-2
This is probably obvious, but I have this in a plpgsql function, where GROUPINGS is a text[]:

SQLSTR := 'SELECT foo,'|| 'bar' = any(GROUPINGS) || ', col2, col3...';

I end up with SQLSTR containin the literal any() statement: SELECT foo, '|| 'bar' = any(GROUPINGS) || ', col2, col3

vs it being coming out like SELECT foo, t, col2, col3.

What am I missing here?

--
Wells Oliver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: plpgsql & string building

David G Johnston
On Sun, Feb 9, 2020 at 4:12 PM Wells Oliver <[hidden email]> wrote:
This is probably obvious, but I have this in a plpgsql function, where GROUPINGS is a text[]:

SQLSTR := 'SELECT foo,'|| 'bar' = any(GROUPINGS) || ', col2, col3...';

I end up with SQLSTR containin the literal any() statement: SELECT foo, '|| 'bar' = any(GROUPINGS) || ', col2, col3

vs it being coming out like SELECT foo, t, col2, col3.

What am I missing here?

Features that make writing this kind of dynamic SQL much easier and more reliable.

Specifically, "format()".  Also, using "EXECUTE" and parameters to pass in external data.

Not Tested, But:

sqlcmd := format($cmd$ SELECT foo, bar = any($1), col2, col3 $cmd$);
EXECUTE sqlcmd USING GROUPINGS;

David J.