Re: pass non-formated query to PL function - Mailing list pgsql-general

On 2021-04-02 Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.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