Passing row set into PL/pgSQL function.

Поиск
Список
Период
Сортировка
От Lucas Clemente Vella
Тема Passing row set into PL/pgSQL function.
Дата
Msg-id CAGCathzw+1PwQsCqcMfP4=47S7KrSHFG=sQSqhG+iFcy1awgXA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Passing row set into PL/pgSQL function.  (Craig Ringer <ringerc@ringerc.id.au>)
Re: Passing row set into PL/pgSQL function.  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-general
I am trying to write a generic "upsert" function in PL/pgSQL, in a way
that I can specify the table were I want to insert/update, the columns
whose values I want to specify, and the values to be inserted.

So far I have come up with a solution whose signature is:

CREATE OR REPLACE FUNCTION upsert(IN tname text, IN cnames text[],
VARIADIC vals anyarray) RETURNS void

Whose tname is the table, cnames are the columns ans vals the values.
The problem I have is when I try to call the function: I can only pass
values of a previously defined type, like:

SELECT upsert('my_table', ARRAY['key', 'data'], (10,
'hello')::my_table, (20, 'world')::my_table);

Instead of:

SELECT upsert('my_table', ARRAY['key', 'data'], (10, 'hello'), (20, 'world'));

What gives me the error:

ERROR:  PL/pgSQL functions cannot accept type record[]

This later approach would be much preferable, since I don't always
want to specify the full table row, but just some fields, and I would
be able to specify the columns in any order I want (as given in
cnames). Since PL/pgSQL is unable to receive a record[] parameter, is
there any alternative for passing a set of arbitrary compound values?
Is there any way of passing a table, like "VALUES (10, 'hello'), (20,
'world')" or a CTE?

Could I use any other language that does not require superuser
privileges to be installed? PL/pgSQL is preferable due to
availability, but using another language would be OK.

--
Lucas Clemente Vella
lvella@gmail.com


В списке pgsql-general по дате отправления:

Предыдущее
От: Gavin Flower
Дата:
Сообщение: Re: Difference between ON and WHERE in JOINs
Следующее
От: Craig Ringer
Дата:
Сообщение: Getting a leading zero on negative intervals with to_char?