Thread: pass non-formated query to PL function
Hello all,
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 ?
The caller does not have access to the variables/values that will be used to fill the blanks, but it does know the generic query.
I have a situation where this kind of separation would be nice to have in order to make the code a bit cleaner and avoid some code repetitions.
Thank you for you suggestions
Joao
Hi
pá 2. 4. 2021 v 11:35 odesílatel Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com> napsal:
Hello all,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 ?The caller does not have access to the variables/values that will be used to fill the blanks, but it does know the generic query.I have a situation where this kind of separation would be nice to have in order to make the code a bit cleaner and avoid some code repetitions.
It is not problem
create or replace function exec_query(q text)
returns void as $$
declare r int;
begin
raise notice 'q=%', q;
execute q using 10 into r;
raise notice 'r=%', r;
end;
$$ language plpgsql;
returns void as $$
declare r int;
begin
raise notice 'q=%', q;
execute q using 10 into r;
raise notice 'r=%', r;
end;
$$ language plpgsql;
postgres=# select exec_query ('select 10 + $1');
NOTICE: q=select 10 + $1
NOTICE: r=20
┌────────────┐
│ exec_query │
╞════════════╡
│ │
└────────────┘
(1 row)
NOTICE: q=select 10 + $1
NOTICE: r=20
┌────────────┐
│ exec_query │
╞════════════╡
│ │
└────────────┘
(1 row)
Regards
Pavel
Thank you for you suggestionsJoao
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
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@thenautilus.net> wrote:
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