Re: Passing row set into PL/pgSQL function.

On Wed, Sep 19, 2012 at 4:37 PM, Lucas Clemente Vella <lvella(at)gmail(dot)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