Postgres Pro
Downloads
Home   >   mailing lists

Re: plpgsql & string building - Mailing list pgsql-admin

From David G. Johnston
Subject Re: plpgsql & string building
Date
Msg-id CAKFQuwb0Lv14-EwWzN=eauYmD9yooLOSzaZ8EZgutRWEtebc=Q@mail.gmail.com
Whole thread Raw
In response to plpgsql & string building  (Wells Oliver <wells.oliver@gmail.com>)
List pgsql-admin
Tree view
On Sun, Feb 9, 2020 at 4:12 PM Wells Oliver <wells.oliver@gmail.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.

pgsql-admin by date:

Previous
From: Wells Oliver
Date:
Subject: plpgsql & string building
Next
From: Sanjib Mohanty
Date:
Subject: Re: pg_basebackup fails to connect from slave server