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 13 Mar 2010 12:00 2010/3/13 Tom Lane <tgl(a)sss.pgh.pa.us>: > Merlin Moncure <mmoncure(a)gmail.com> writes: >> On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl(a)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 > -- 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: Tom Lane on 13 Mar 2010 12:18 Merlin Moncure <mmoncure(a)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 -- 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 13 Mar 2010 12:31 On Sat, Mar 13, 2010 at 12:18:32PM -0500, Tom Lane wrote: > Merlin Moncure <mmoncure(a)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(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: Andrew Dunstan on 13 Mar 2010 12:31 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 -- 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: Tom Lane on 13 Mar 2010 12:38
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |