Thread: Dyamic updates of NEW with pl/pgsql
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. Thanks in advance. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html
2010/3/9 strk <strk@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 regards Pavel Stehule > > Thanks in advance. > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: > 2010/3/9 strk <strk@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 Any workaround you may suggest ? --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html
2010/3/9 strk <strk@keybit.net>: > On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: >> 2010/3/9 strk <strk@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 > > Any workaround you may suggest ? I don't know it - use C language maybe. Pavel > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html >
On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: > 2010/3/9 strk <strk@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/ depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
hubert depesz lubaczewski wrote: > On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: > >> 2010/3/9 strk <strk@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. cheers andrew
> 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. It's not possible in plpgsql, but you can write plperl function, and later use it in plpgsql triggers. Regards, Dmitry
On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote: > Using an hstore in 9.0 it's not too bad, Does it still have a limit of 65535 bytes per field ? --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html
On Thu, Mar 11, 2010 at 03:27:23PM +0100, strk wrote: > On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote: > > > Using an hstore in 9.0 it's not too bad, > > Does it still have a limit of 65535 bytes per field ? No. :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
On Wed, Mar 10, 2010 at 7:50 AM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> 2010/3/9 strk <strk@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. > > Using an hstore in 9.0 it's not too bad, Try something like: > Agree 100%. The new hstore going to completely nail a broad class of issues that have historically been awkward in plpgsql functions. (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. merlin
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? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Thu, Mar 11, 2010 at 11:24 PM, Alvaro Herrera <alvherre@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
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@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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
2010/3/12 David Fetter <david@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@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@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@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 >
On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote: > 2010/3/12 David Fetter <david@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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
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@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
2010/3/12 strk <strk@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@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 >
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@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/
On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@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
Merlin Moncure írta: > On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@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/
On Fri, Mar 12, 2010 at 4:24 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote: > Merlin Moncure írta: >> On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@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
Pavel Stehule wrote: > 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. > > Yes, the use of hstore that I illustrated upthread is a workaround, not a real solution. Having said that, it works pretty darn well in my experience. I think we need some operator on records+strings for this functionality. Something like (say we used "->"): foo := 'myfieldname'; myrec->foo := 'bar'; quux := myrec->foo; I agree that if we were to include hstore in core it needs a better name (we do need to be careful about this stuff, I know the name "bytea" confuses even seasoned users). And in any case, before we rush headlong into incorporating hstore, we should consider its limitations, particularly the fact that it's a flat map, rather than something that composes like, say, some sort of JSON object. There have certainly been times when I would have appreciated the latter. (But in case there is any misunderstanding, let me say that hstore is really great and useful. I have thanked Oleg and Teodor and Andrew many times in my head.) cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> I think we need some operator on records+strings for this functionality.
I don't see how you're going to do that without utterly compromising the
type system.
It's not so horrid to do this type of thing in plperl, pltcl etc because
you've already bought into an "everything is text" worldview when you
use those languages. But plpgsql is strongly typed just like SQL is,
and I don't think we should undo that.
(This will also be my main objection to letting hstore into core.
It has not solved the problem of handling real datatypes.)
regards, tom lane
On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > (This will also be my main objection to letting hstore into core. > It has not solved the problem of handling real datatypes.) Is this problem solvable then? Some variant of this question comes up almost weekly. It just doesn't seem right that you should have to write N trigger functions over N tables to a highly related operations. pl/perl is a huge dependency to bring in just to able to do things this. I understand hacking things through the text route is possibly not a direction should be encouraged...but is there an alternative? Is it theoretically possible to write functions that can switch out types based on context while still having static plans? merlin
2010/3/13 Tom Lane <tgl@sss.pgh.pa.us>: > Andrew Dunstan <andrew@dunslane.net> writes: >> I think we need some operator on records+strings for this functionality. > > I don't see how you're going to do that without utterly compromising the > type system. > > It's not so horrid to do this type of thing in plperl, pltcl etc because > you've already bought into an "everything is text" worldview when you > use those languages. But plpgsql is strongly typed just like SQL is, > and I don't think we should undo that. > strong typing isn't problem for field updating - and we can do necessary conversion to target type - for simple expression (without cached plan). I like some var = record[expression]; record[expression] = var; I don't thing so current static naturel of plpgsql is impossible problem. It needs just more inteligent assign statement. Pavel > (This will also be my main objection to letting hstore into core. > It has not solved the problem of handling real datatypes.) > > regards, tom lane >
Merlin Moncure <mmoncure@gmail.com> writes:
> On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (This will also be my main objection to letting hstore into core.
>> It has not solved the problem of handling real datatypes.)
> Is this problem solvable then?
I don't know, but hstore hasn't even tried. We should be very slow
to institutionalize a "smash everything to text" approach in core.
regards, tom lane
2010/3/13 Tom Lane <tgl@sss.pgh.pa.us>: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> (This will also be my main objection to letting hstore into core. >>> It has not solved the problem of handling real datatypes.) > >> Is this problem solvable then? > > I don't know, but hstore hasn't even tried. We should be very slow > to institutionalize a "smash everything to text" approach in core. I agree - text everywhere is bad way Pavel > > regards, tom lane >
Merlin Moncure <mmoncure@gmail.com> writes:
> ... It just doesn't seem right that you should have to
> write N trigger functions over N tables to a highly related
> operations. pl/perl is a huge dependency to bring in just to able to
> do things this. I understand hacking things through the text route is
> possibly not a direction should be encouraged...but is there an
> alternative? Is it theoretically possible to write functions that can
> switch out types based on context while still having static plans?
[ after a little bit of reflection ]
ISTM that in most cases where this is a serious issue, the trigger
functions are doing the *same* thing to different tables. Not just
textually the same, but datatype-wise the same. So I'm not sure I
believe that we need to be able to "switch out types". Maybe it would
work to devise a notation that allows fetching or storing a field that
has a runtime-determined name, but prespecifies the field type.
Actually only the "fetch" end of it is an issue, since when storing the
field datatype can be inferred from the expression you're trying to
assign to the field.
regards, tom lane
On Sat, Mar 13, 2010 at 12:18:32PM -0500, Tom Lane wrote: > Merlin Moncure <mmoncure@gmail.com> writes: > > ... It just doesn't seem right that you should have to write N > > trigger functions over N tables to a highly related operations. > > pl/perl is a huge dependency to bring in just to able to do things > > this. I understand hacking things through the text route is > > possibly not a direction should be encouraged...but is there an > > alternative? Is it theoretically possible to write functions that > > can switch out types based on context while still having static > > plans? > > [ after a little bit of reflection ] > > ISTM that in most cases where this is a serious issue, the trigger > functions are doing the *same* thing to different tables. Yes. Well, at least the same base type. I don't suppose now is a great time to get into the second class status of domains. :P Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
Tom Lane wrote: > > ISTM that in most cases where this is a serious issue, the trigger > functions are doing the *same* thing to different tables. Not just > textually the same, but datatype-wise the same. So I'm not sure I > believe that we need to be able to "switch out types". Maybe it would > work to devise a notation that allows fetching or storing a field that > has a runtime-determined name, but prespecifies the field type. > Actually only the "fetch" end of it is an issue, since when storing the > field datatype can be inferred from the expression you're trying to > assign to the field. > > > That's exactly the sort of thing I had in mind. I wasn't talking about loosening the type system. Classic case: you want to set/update a timestamp field in the NEW record, but it might not be called the same thing on each table, so you pass the field name as a trigger argument. cheers andrew
I wrote:
> ... Maybe it would
> work to devise a notation that allows fetching or storing a field that
> has a runtime-determined name, but prespecifies the field type.
> Actually only the "fetch" end of it is an issue, since when storing the
> field datatype can be inferred from the expression you're trying to
> assign to the field.
[ after more thought ]
I wonder if it could work to treat the result of a "record->fieldname"
operator as being of UNKNOWN type initially, and resolve its actual
type in the parser in the same way we do for undecorated literals
and parameters, to wit* you can explicitly cast it, viz (record->fieldname)::bigint* you can let it be inferred from
context,such as the type of whatever it's compared to* throw error if type is not inferrable
Then at runtime, if the actual type of the field turns out to not be
what the parser inferred, either throw error or attempt a run-time
type coercion. Throwing error seems safer, because it would avoid
surprises of both semantic (unexpected behavior) and performance
(expensive conversion you weren't expecting to happen) varieties.
But possibly an automatic coercion would be useful enough to justify
those risks.
BTW the same coerce-or-throw-error choice would arise on the "store"
side, if the expression to be stored turns out to not exactly match
the field type.
regards, tom lane
On Sat, Mar 13, 2010 at 1:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I wonder if it could work to treat the result of a "record->fieldname" > operator as being of UNKNOWN type initially, and resolve its actual > type in the parser in the same way we do for undecorated literals > and parameters, to wit > * you can explicitly cast it, viz > (record->fieldname)::bigint > * you can let it be inferred from context, such as the type > of whatever it's compared to > * throw error if type is not inferrable > Then at runtime, if the actual type of the field turns out to not be > what the parser inferred, either throw error or attempt a run-time > type coercion. Throwing error seems safer, because it would avoid > surprises of both semantic (unexpected behavior) and performance > (expensive conversion you weren't expecting to happen) varieties. > But possibly an automatic coercion would be useful enough to justify > those risks. the casting rules are completely reasonable. Throwing an error seems like a better choice. Better to be strict now and relax the rules later. record->fieldname takes a string (possibly a variable)? If so, his would nail the problem. This would work with run time typed records (new, etc)? merlin
Merlin Moncure wrote: > record->fieldname takes a string (possibly a variable)? If it doesn't we have a communication problem. :-) > If so, his would nail the problem. Not quite, but close. We also need a nice way of querying for field names (at least) at run time. I've seen that requested several times. > This would work with run time typed > records (new, etc)? > > Again, if it doesn't we have a communication problem. cheers andrew
On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Not quite, but close. We also need a nice way of querying for field names
> (at least) at run time. I've seen that requested several times.
ok. just making sure we were on the same page. wasn't there a
technical objection to querying the fields at runtime? If not, maybe
you could get by with something like:
Integer variant of operator pulls fields by index
somettype v := recvar->3;
integer n := nfields(recordtype);
text[] fields := fieldnames(recordtype);
text fieldname := fieldname(recordtype, 3);
int fieldpos := fieldpos(recordtype, 'a_field');
OK, from archives (Tom wrote) quoting:
So, inventing syntax at will, what you're imagining is something like
modified := false; for name in names(NEW) loop -- ignore modified_timestamp
continueif name = 'modified_timestamp'; -- check all other columns if NEW.{name} is distinct
fromOLD.{name} then modified := true; exit; end if; end
loop; if modified then ...
While this is perhaps doable, the performance would take your breath
away ... and I don't mean that in a positive sense. The only way we
could implement that in plpgsql as it stands would be that every
single execution of the IF would invole a parse/plan cycle for the
"$1 IS DISTINCT FROM $2" expression. At best we would avoid a replan
when successive executions had the same datatypes for the tested
columns (ie, adjacent columns in the table have the same types).
Which would happen some of the time, but the cost of the replans would
still be enough to sink you.
/end quote
does the parse/plan objection still hold?
merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> Not quite, but close. We also need a nice way of querying for field names
>> (at least) at run time. I've seen that requested several times.
> does the parse/plan objection still hold?
Yeah. Providing the field names isn't the dubious part --- the dubious
part is what are you going to *do* with them. It's difficult to see
applications in which you can make the simplifying assumption that the
actual field datatypes are known/fixed. Using field numbers instead of
names doesn't get you out from under that. (Though I like the idea
insofar as it simplifies the looping mechanism.)
If we make the implementation be such that "(rec->field)::foo" forces
a runtime cast to foo (rather than throwing an error if it's not type
foo already), then it's possible to suppose that this sort of application
could be catered to by forcing all the fields to text, or some other
generic datatype. This at least puts the text dependency out where the
user can see it, though it still seems rather inelegant. It also takes
away possible error detection in other circumstances where a forced cast
isn't really wanted.
The cost of looking up the ever-changing cast function could still be
unpleasant, although I think we could hide it in the executor expression
node instead of forcing a whole new parse/plan cycle each time.
regards, tom lane
On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > If we make the implementation be such that "(rec->field)::foo" forces a runtime cast to foo (rather than throwing an error if it's not type foo already) yeah...explicit cast should always do 'best effort' > The cost of looking up the ever-changing cast function could still be > unpleasant, although I think we could hide it in the executor expression > node instead of forcing a whole new parse/plan cycle each time. right. if you do that, it's still going to be faster than the dyna-sql/information schema/perl hacks people are doing right now (assuming they didn't give up and code it in the app). This is rtti for plpgsql, and functions that use it are going have to be understood as being slower and to be avoided if possible, like exception handlers. IMNSHO, this is a small price to pay. merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If we make the implementation be such that "(rec->field)::foo" forces
>> a runtime cast to foo (rather than throwing an error if it's not type
>> foo already)
> yeah...explicit cast should always do 'best effort'
Probably so. But is it worth inventing some other notation that says
"expect this field to be of type foo", with an error rather than runtime
cast if it's not? If we go with treating the result of -> like UNKNOWN,
then you wouldn't need that in cases where the parser guesses the right
type. But there are going to be cases where you need to override the
guess without necessarily wanting to buy into a forced conversion.
regards, tom lane
On Mon, Mar 15, 2010 at 12:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> If we make the implementation be such that "(rec->field)::foo" forces >>> a runtime cast to foo (rather than throwing an error if it's not type >>> foo already) > >> yeah...explicit cast should always do 'best effort' > > Probably so. But is it worth inventing some other notation that says > "expect this field to be of type foo", with an error rather than runtime > cast if it's not? If we go with treating the result of -> like UNKNOWN, > then you wouldn't need that in cases where the parser guesses the right > type. But there are going to be cases where you need to override the > guess without necessarily wanting to buy into a forced conversion. Maybe. That behaves like oid vector to PQexecParams, right? Suggests a type but does not perform a cast. I see your point but I think it's going to go over the heads of most people...type association vs type coercion. Maybe instead you could just supply typeof function in order to provide very rigorous checking when wanted and presumably allow things like pointing the assignment at a special field. merlin
On 13.03.10 18:38 , Tom Lane wrote: > I wrote: >> ... Maybe it would work to devise a notation that allows fetching >> or storing a field that has a runtime-determined name, but >> prespecifies the field type. Actually only the "fetch" end of it is >> an issue, since when storing the field datatype can be inferred >> from the expression you're trying to assign to the field. > > [ after more thought ] > > I wonder if it could work to treat the result of a > "record->fieldname" operator as being of UNKNOWN type initially, and > resolve its actual type in the parser in the same way we do for > undecorated literals and parameters, to wit * you can explicitly cast > it, viz (record->fieldname)::bigint * you can let it be inferred from > context, such as the type of whatever it's compared to * throw error > if type is not inferrable Then at runtime, if the actual type of the > field turns out to not be what the parser inferred, either throw > error or attempt a run-time type coercion. Throwing error seems > safer, because it would avoid surprises of both semantic (unexpected > behavior) and performance (expensive conversion you weren't expecting > to happen) varieties. But possibly an automatic coercion would be > useful enough to justify those risks. This is more or less what I've done in my pg_record_inspect module, only without parser or executor changes (it works with 8.4). The code can be found on http://github.com/fgp/pg_record_inspect. The module contains the function fieldvalue(RECORD, field NAME, defval ANYELEMENT, coerce BOOLEAN) RETURNS ANYELEMENT which returns the field named <field> from the record. The expected field type is specified by providing a default value in <defval> of the expected type. Since that argument's type is ANYELEMENT, just like the return type, the type system copes perfectly with the varying return type. You can choose whether to auto-coerce the field's value if it has a type other than <defval>'s type or whether to raise an error. So in essence I'm using the ANYELEMENT trick to get a poor man's version of your idea that doesn't require core changes. My post about this module got zero responses though... best regards, Florian Pflug
On Tue, Mar 16, 2010 at 5:53 PM, Florian Pflug <fgp.phlo.org@gmail.com> wrote: > which returns the field named <field> from the record. The expected > field type is specified by providing a default value in <defval> of the > expected type. Since that argument's type is ANYELEMENT, just like the > return type, the type system copes perfectly with the varying return > type. You can choose whether to auto-coerce the field's value if it has > a type other than <defval>'s type or whether to raise an error. > > So in essence I'm using the ANYELEMENT trick to get a poor man's version > of your idea that doesn't require core changes. > > My post about this module got zero responses though... Why should we use what you've already written when we can just write it ourselves? Next you are going to say you're already using it and it works really well :-). I think it's pretty cool. Is it safe to have the main functions immutable and not stable though? Is there any benefit missed by not going through pl/pgsql directly (I'm guessing maybe more elegant caching)? It's a little weird that you can return anyelement from your function in cases that don't guarantee a type from the query. Are there any downsides to doing that? merlin
On 17.03.10 4:08 , Merlin Moncure wrote: > On Tue, Mar 16, 2010 at 5:53 PM, Florian > Pflug<fgp.phlo.org@gmail.com> wrote: >> which returns the field named<field> from the record. The >> expected field type is specified by providing a default value >> in<defval> of the expected type. Since that argument's type is >> ANYELEMENT, just like the return type, the type system copes >> perfectly with the varying return type. You can choose whether to >> auto-coerce the field's value if it has a type other than<defval>'s >> type or whether to raise an error. >> >> So in essence I'm using the ANYELEMENT trick to get a poor man's >> version of your idea that doesn't require core changes. >> >> My post about this module got zero responses though... > > Why should we use what you've already written when we can just write > it ourselves? Next you are going to say you're already using it and > it works really well :-). Well, compared to the solution it replaced it works extraordinarily well - but that solution was a mess of plpgsql functions generating other plpgsql functions - so shining in comparison doesn't really prove much :-) > I think it's pretty cool. Is it safe to have the main functions > immutable and not stable though? I think it's safe - if a table or composite type is modified, a query using that table or type will have to be re-planned anyway, independent from whether fieldvalue() is used or not. > Is there any benefit missed by not going through pl/pgsql directly > (I'm guessing maybe more elegant caching)? AFAIK in pl/pgsql your only options to retrieve a field by name is to either use hstore which coerces all values to text, or to use EXECUTE 'SELECT %1' || v_fieldname INTO v_fieldvalue USING v_record. The execute query will need to be planned on every execution, while my fieldvalue() function tries to cache as much information as possible. The EXECUTE method will also always coerce the field's value to the type of v_fieldvalue - AFAICS there is no way to get the behaviour of fieldvalue() with <coerce> set to false. > It's a little weird that you can return anyelement from your function > in cases that don't guarantee a type from the query. Are there any > downsides to doing that? Hm, the type of fieldvalue()'s return value is always the same as the one of the ANYELEMENT input value <defvalue>. If <coerce> is true, then the field value's type may be different, but fieldvalue() takes care of coercing it to <defvalue>'s type *before* returning it. So from a type system's perspective, fieldvalue() plays entirely by the rules. The only open issue in my code is the caching of the coercion plans - currently, they're cached in fcinfo->flinfo->fn_extra, and never invalidated. I believe the plan invalidation machinery might make it possible to invalidate those plans should the CAST definitions change, but I haven't really looked into that yet. best regards, Florian Pflug