Thread: Passing row set into PL/pgSQL function.

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

On 09/20/2012 05:37 AM, Lucas Clemente Vella wrote:
> 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.

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

--
Craig Ringer

On 09/20/2012 01:47 PM, Lucas Clemente Vella wrote:
>> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
>
> I have already seen this page, I am OK in running SERIALIZABLE
> transactions, and have no problem in replaying failed transactions due
> to race condition. Anyway, that is completely off my issue: I need
> upsert and I am prepared to deal with it. I just want to save typing
> by creating a reusable function.

In that case, maybe you could have your function accept a `refcursor`?

DECLARE some_curs CURSOR FOR VALUES ('a',1), ('b',2), ('c',3);
SELECT funky_upsert('table', ARRAY['col1','col2'], 'some_curs');
CLOSE some_curs;

Internally it could fetch rows from the refcursor into record fields and
do what it needed.

Personally I'd just do the work app-side.

--
Craig Ringer

On Wed, Sep 19, 2012 at 4:37 PM, Lucas Clemente Vella <lvella@gmail.com> wrote:
> 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[]

note, pl/pgsql functions can take arrays of non-anonymous record types
-- either tables, or composite types.  you're just not allowed to pass
anonymous rows in.

for key value pairs, also you should take a look at hstore.  You can
also make arrays of hstore.

merlin

> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

I have already seen this page, I am OK in running SERIALIZABLE
transactions, and have no problem in replaying failed transactions due
to race condition. Anyway, that is completely off my issue: I need
upsert and I am prepared to deal with it. I just want to save typing
by creating a reusable function.

--
Lucas Clemente Vella
lvella@gmail.com