Passing row set into PL/pgSQL function.

From: Lucas Clemente Vella <lvella(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Passing row set into PL/pgSQL function.
Date: 2012-09-19 21:37:41
Message-ID: CAGCathzw+1PwQsCqcMfP4=47S7KrSHFG=sQSqhG+iFcy1awgXA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)gmail(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Flower 2012-09-19 21:50:09 Re: Difference between ON and WHERE in JOINs
Previous Message hubert depesz lubaczewski 2012-09-19 20:15:06 Why csvlog logs contexts without leading tab?