Re: pass non-formated query to PL function

Hi

pá 2. 4. 2021 v 11:35 odesílatel Joao Miguel Ferreira <
joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)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;

postgres=# select exec_query ('select 10 + $1');
NOTICE: q=select 10 + $1
NOTICE: r=20
┌────────────┐
│ exec_query │
╞════════════╡
│ │
└────────────┘
(1 row)

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards

Pavel

>
> Thank you for you suggestions
> Joao
>
>