From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: plpgsql & string building |
Date: | 2020-02-10 00:12:03 |
Message-ID: | CAKFQuwb0Lv14-EwWzN=eauYmD9yooLOSzaZ8EZgutRWEtebc=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sanjib Mohanty | 2020-02-10 04:58:24 | Re: pg_basebackup fails to connect from slave server |
Previous Message | Wells Oliver | 2020-02-09 23:12:07 | plpgsql & string building |