Re: plpgsql & string building

On Sun, Feb 9, 2020 at 4:12 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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.