Re: Passing row set into PL/pgSQL function.
От | Merlin Moncure |
---|---|
Тема | Re: Passing row set into PL/pgSQL function. |
Дата | |
Msg-id | CAHyXU0z5DMyDGA370tRZaus_gzT+cydQWUrk1VWnP+bXQU7f9A@mail.gmail.com обсуждение исходный текст |
Ответ на | Passing row set into PL/pgSQL function. (Lucas Clemente Vella <lvella@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: