Re: pass non-formated query to PL function
| От | Pavel Stehule |
|---|---|
| Тема | Re: pass non-formated query to PL function |
| Дата | |
| Msg-id | CAFj8pRDbCL9KQfUcGqhZKfARZ6ayvDsP-ZR_5hHJMVcFH1UpPg@mail.gmail.com обсуждение исходный текст |
| Ответ на | pass non-formated query to PL function (Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.com>) |
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: