Re: pass non-formated query to PL function

Hi Pavel and Gianni,

Thank you for the suggestions and documentation. That is exactly the kind
of thing I was looking for.

Cool, thx
Joao

On Fri, Apr 2, 2021 at 10:54 AM Gianni Ceccarelli <dakkar(at)thenautilus(dot)net>
wrote:

> On 2021-04-02 Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>
> wrote:
> > Is it possible, in PL/pgSQL, to pass an argument to a function which
> > is actually a "query skeleton" that the method will "fill in the
> > blanks" and execute it or return it to the caller after ?
>
> you probably want to use the ``EXECUTE`` command:
>
> https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Something like this, maybe::
>
> CREATE FUNCTION run_me_this(in the_query text) RETURNS record
> AS $$
> DECLARE
> this record;
> BEGIN
> EXECUTE the_query INTO this USING 1, 'foo';
> RETURN this;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TABLE test(id serial primary key,name text,value text);
> INSERT INTO test(name,value) VALUES ('foo','something');
>
> SELECT *
> FROM run_me_this('select value from test where id=$1 and name=$2')
> x(value text);
>
> Notice, though, that ``EXECUTE ... INTO`` will only assign *the first
> row* of the results to the given variable (``this`` in my
> example). I'm not sure how to work around this limitation.
>
> --
> Dakkar - <Mobilis in mobile>
> GPG public key fingerprint = A071 E618 DD2C 5901 9574
> 6FE2 40EA 9883 7519 3F88
> key id = 0x75193F88
>
>
>
>