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

Search Discussions

  • Pavel Stehule at Mar 9, 2010 at 5:59 pm

    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
  • Strk at Mar 9, 2010 at 7:27 pm

    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
  • Pavel Stehule at Mar 9, 2010 at 7:27 pm

    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
  • Hubert depesz lubaczewski at Mar 10, 2010 at 11:38 am

    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
  • Andrew Dunstan at Mar 10, 2010 at 12:50 pm

    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
  • Strk at Mar 11, 2010 at 2:28 pm

    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
  • David Fetter at Mar 11, 2010 at 3:04 pm

    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
  • Merlin Moncure at Mar 12, 2010 at 4:09 am

    On Wed, Mar 10, 2010 at 7:50 AM, Andrew Dunstan 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
  • Alvaro Herrera at Mar 12, 2010 at 4:25 am

    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.
  • Merlin Moncure at Mar 12, 2010 at 12:32 pm

    On Thu, Mar 11, 2010 at 11:24 PM, Alvaro Herrera 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
  • David Fetter at Mar 12, 2010 at 4:55 pm

    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
  • Pavel Stehule at Mar 12, 2010 at 6:35 pm

    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
  • David Fetter at Mar 12, 2010 at 6:47 pm

    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
  • Strk at Mar 12, 2010 at 6:53 pm

    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
  • Pavel Stehule at Mar 12, 2010 at 6:59 pm

    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
  • Andrew Dunstan at Mar 13, 2010 at 2:20 pm

    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
  • Tom Lane at Mar 13, 2010 at 4:43 pm

    Andrew Dunstan 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
  • Merlin Moncure at Mar 13, 2010 at 4:55 pm

    On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane 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
  • Tom Lane at Mar 13, 2010 at 4:59 pm

    Merlin Moncure writes:
    On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane 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
  • Pavel Stehule at Mar 13, 2010 at 5:00 pm

    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 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
  • Tom Lane at Mar 13, 2010 at 5:21 pm

    Merlin Moncure 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
  • David Fetter at Mar 13, 2010 at 5:31 pm

    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
  • Andrew Dunstan at Mar 13, 2010 at 5:32 pm

    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
  • Tom Lane at Mar 13, 2010 at 5:39 pm

    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
  • Merlin Moncure at Mar 15, 2010 at 1:40 pm

    On Sat, Mar 13, 2010 at 1:38 PM, Tom Lane 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
  • Andrew Dunstan at Mar 15, 2010 at 2:02 pm

    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
  • Merlin Moncure at Mar 15, 2010 at 3:18 pm

    On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan 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
    continue if name = 'modified_timestamp';
    -- check all other columns
    if NEW.{name} is distinct from OLD.{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
  • Tom Lane at Mar 15, 2010 at 3:39 pm

    Merlin Moncure writes:
    On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan 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
  • Merlin Moncure at Mar 15, 2010 at 4:53 pm

    On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane 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
  • Tom Lane at Mar 15, 2010 at 4:54 pm

    Merlin Moncure writes:
    On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane 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
  • Merlin Moncure at Mar 15, 2010 at 4:59 pm

    On Mon, Mar 15, 2010 at 12:19 PM, Tom Lane wrote:
    Merlin Moncure <mmoncure@gmail.com> writes:
    On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane 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
  • Florian Pflug at Mar 16, 2010 at 10:00 pm

    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
  • Merlin Moncure at Mar 17, 2010 at 3:08 am

    On Tue, Mar 16, 2010 at 5:53 PM, Florian Pflug 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
  • Florian Pflug at Mar 17, 2010 at 1:12 pm

    On 17.03.10 4:08 , Merlin Moncure wrote:
    On Tue, Mar 16, 2010 at 5:53 PM, Florian
    Pflugwrote:
    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
  • Pavel Stehule at Mar 13, 2010 at 4:58 pm

    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
  • Boszormenyi Zoltan at Mar 12, 2010 at 8:02 pm

    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/
  • Merlin Moncure at Mar 12, 2010 at 8:17 pm

    On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan 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
  • Boszormenyi Zoltan at Mar 12, 2010 at 9:26 pm

    Merlin Moncure írta:
    On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan 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/
  • Robert Haas at Mar 12, 2010 at 10:41 pm

    On Fri, Mar 12, 2010 at 4:24 PM, Boszormenyi Zoltan wrote:
    Merlin Moncure írta:
    On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan 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
  • Dmitry Fefelov at Mar 11, 2010 at 10:25 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 9, '10 at 5:32p
activeMar 17, '10 at 1:12p
posts41
users12
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase