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
