Re: [GENERAL] Passing row set into PL/pgSQL function.
> 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 lve...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing row set into PL/pgSQL function.
On Wed, Sep 19, 2012 at 4:37 PM, 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.
>
> 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing row set into PL/pgSQL function.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing row set into PL/pgSQL function.
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
