Dyamic updates of NEW with pl/pgsql
in [PgSql]
|
Prev: patch (for 9.1) string functions
Next: [HACKERS] Access violation from palloc, Visual Studio 2005, C-language function
From: Merlin Moncure on 12 Mar 2010 07:32 On Thu, Mar 11, 2010 at 11:24 PM, Alvaro Herrera <alvherre(a)commandprompt.com> wrote: > Merlin Moncure escribió: > > >> (small aside: the other biggie would be able to push a composite type >> in to an update statement...something like 'update foo set foo = >> new'). This is really great...some variant of this question is >> continually asked it seems. > > Can't you already do that with EXECUTE ... USING NEW? hmm, ah, but you > have to specify the columns in NEW, so it doesn't really work for you, > does it? right...with inserts you can expand the composite type without listing the columns. updates can't do it because of syntax issues, even if you go dynamic. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: David Fetter on 12 Mar 2010 11:54 On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote: > hubert depesz lubaczewski wrote: > >On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: > >>2010/3/9 strk <strk(a)keybit.net>: > >>>How can a pl/pgsql trigger change the > >>>values of dynamic fields in NEW record ? > >>> > >>>By "dynamic" I mean that the field name > >>>is a variable in the trigger context. > >>> > >>>I've been told it's easy to do with pl/perl but > >>>I'd like to delive a pl/pgsql solution to have > >>>less dependencies. > >>It isn't possible yet > > > >well, it's possible. it's just not nice. > > > >http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/ > > Using an hstore in 9.0 it's not too bad, Try something like: > > CREATE OR REPLACE FUNCTION dyntrig() > RETURNS trigger > LANGUAGE plpgsql > AS $function$ > > declare > hst hstore; > begin > hst := hstore(NEW); > hst := hst || ('foo' => 'bar'); > NEW := populate_record(NEW,hst); > return NEW; > end; > > $function$; > > But this question probably belongs on -general rather than -hackers. This is, by the way, an excellent argument for including hstore in core in 9.1. :) Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 12 Mar 2010 13:35 2010/3/12 David Fetter <david(a)fetter.org>: > On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote: >> hubert depesz lubaczewski wrote: >> >On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: >> >>2010/3/9 strk <strk(a)keybit.net>: >> >>>How can a pl/pgsql trigger change the >> >>>values of dynamic fields in NEW record ? >> >>> >> >>>By "dynamic" I mean that the field name >> >>>is a variable in the trigger context. >> >>> >> >>>I've been told it's easy to do with pl/perl but >> >>>I'd like to delive a pl/pgsql solution to have >> >>>less dependencies. >> >>It isn't possible yet >> > >> >well, it's possible. it's just not nice. >> > >> >http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/ >> >> Using an hstore in 9.0 it's not too bad, Try something like: >> >>   CREATE OR REPLACE FUNCTION dyntrig() >>   RETURNS trigger >>   LANGUAGE plpgsql >>   AS $function$ >> >>   declare >>       hst hstore; >>   begin >>       hst := hstore(NEW); >>       hst := hst || ('foo' => 'bar'); >>       NEW := populate_record(NEW,hst); >>       return NEW; >>   end; >> >>   $function$; >> >> But this question probably belongs on -general rather than -hackers. > > This is, by the way, an excellent argument for including hstore in > core in 9.1. :) I like it - but it looking little bit strange - I thinking we need only one function (maybe with some special support from pl executor) begin update_field(NEW, 'field', value); .... Pavel > > Cheers, > David. > -- > David Fetter <david(a)fetter.org> http://fetter.org/ > Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter > Skype: davidfetter    XMPP: david.fetter(a)gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: David Fetter on 12 Mar 2010 13:47 On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote: > 2010/3/12 David Fetter <david(a)fetter.org>: > > > > This is, by the way, an excellent argument for including hstore in > > core in 9.1. :) > > I like it - but it looking little bit strange - I thinking we need > only one function (maybe with some special support from pl executor) > > begin > update_field(NEW, 'field', value); > .... This doesn't seem like a terribly useful addition, it being specific to PL/pgsql. Then there's the quoting issue, which the above doesn't quite address. Putting hstore in would let all the other PLs use it, to the extent that they need such a thing. :) Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: strk on 12 Mar 2010 13:51
On Fri, Mar 12, 2010 at 10:47:45AM -0800, David Fetter wrote: > On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote: > > 2010/3/12 David Fetter <david(a)fetter.org>: > > > > > > This is, by the way, an excellent argument for including hstore in > > > core in 9.1. :) > > > > I like it - but it looking little bit strange - I thinking we need > > only one function (maybe with some special support from pl executor) > > > > begin > > update_field(NEW, 'field', value); > > .... > > This doesn't seem like a terribly useful addition, it being specific > to PL/pgsql. Then there's the quoting issue, which the above doesn't > quite address. Putting hstore in would let all the other PLs use it, > to the extent that they need such a thing. :) Plus pure SQL use ! I was considering using hstore for a table value too for a form of "historic table". Just to say I'd also be happy with it being core in pgsql :) --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |