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: Pavel Stehule on 12 Mar 2010 13:59 2010/3/12 strk <strk(a)keybit.net>: > 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 :) > I see some disadvantages a) non intuitive name - hstore is very specific name b) effectivity (mainly inside trigger body) - plpgsql specific construct can be 10x faster. I would to see hash tables in core too, but I don't think so it is good solution for record updating. Regards Pavel > --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
From: Boszormenyi Zoltan on 12 Mar 2010 15:01 strk �rta: > 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 ! > What's wrong with "UPDATE foo SET (foo) = (NEW);" ? I know it's a little ambiguous, as table "foo" can have fields named "foo" and "new", but the UPDATE foo SET (field, ...) = (value, ...); works in plain SQL and the (...) usually denotes a list with more than one field/value. pl/pgSQL could treat the "list with single name" as a special case (maybe checking whether the table has fields "foo", "new" and/or "old" and issue a warning when relevant) and treat the above as a whole-row update. Best regards, Zolt�n B�sz�rm�nyi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zolt�n B�sz�rm�nyi Cybertec Sch�nig & Sch�nig GmbH http://www.postgresql.at/ -- 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: Merlin Moncure on 12 Mar 2010 15:17 On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb(a)cybertec.at> wrote: > > What's wrong with "UPDATE foo SET (foo) = (NEW);" ? > amen brother! :-) I say though, since you can do: SELECT foo FROM foo; why not UPDATE foo SET foo = new;? 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: Boszormenyi Zoltan on 12 Mar 2010 16:24 Merlin Moncure �rta: > On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb(a)cybertec.at> wrote: > >> What's wrong with "UPDATE foo SET (foo) = (NEW);" ? >> >> > > amen brother! :-) > > I say though, since you can do: > SELECT foo FROM foo; > why not > UPDATE foo SET foo = new;? > I just tried this: zozo=# create table foo (foo integer, bar integer); CREATE TABLE zozo=# insert into foo values (1, 2), (2, 4); INSERT 0 2 zozo=# select foo from foo; foo ----- 1 2 (2 rows) zozo=# create table foo1 (foo integer, bar integer); CREATE TABLE zozo=# insert into foo1 values (1, 2), (2, 4); INSERT 0 2 zozo=# select foo1 from foo1; foo1 ------- (1,2) (2,4) (2 rows) So, if the table has field that's name is the same as the table name then SELECT foo FROM foo; returns the field, not the whole row, it's some kind of a precedence handling. What we could do is the reverse precedence with UPDATE foo SET foo = 3 WHERE foo = 1; vs UPDATE foo SET (foo) = (1,3) WHERE (foo) = (1,2); Note the WHERE condition, I would expect it to work there, too. If it works in plain SQL then no special casing would be needed in PLs. Best regards, Zolt�n B�sz�rm�nyi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zolt�n B�sz�rm�nyi Cybertec Sch�nig & Sch�nig GmbH http://www.postgresql.at/ -- 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: Robert Haas on 12 Mar 2010 17:41
On Fri, Mar 12, 2010 at 4:24 PM, Boszormenyi Zoltan <zb(a)cybertec.at> wrote: > Merlin Moncure írta: >> On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb(a)cybertec.at> wrote: >> >>> What's wrong with "UPDATE foo SET (foo) = (NEW);" ? >>> >>> >> >> amen brother! :-) >> >> I say though, since you can do: >> SELECT foo FROM foo; >> why not >> UPDATE foo SET foo = new;? >> > > I just tried this: > > zozo=# create table foo (foo integer, bar integer); > CREATE TABLE > zozo=# insert into foo values (1, 2), (2, 4); > INSERT 0 2 > zozo=# select foo from foo; > foo > ----- > 1 > 2 > (2 rows) But you can always get around this with, e.g. SELECT v FROM foo v; ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |