Re: plpgsql & string building

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Sun, Feb 9, 2020 at 4:12 PM Wells Oliver <wells.oliver@xxxxxxxxx> 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.