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(at)keybit(dot)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(at)postgresql(dot)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(at)keybit(dot)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(at)keybit(dot)net>:
> On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:
>> 2010/3/9 strk <strk(at)keybit(dot)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(at)keybit(dot)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(at)depesz(dot)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(at)keybit(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)dunslane(dot)net> wrote:
>>> 2010/3/9 strk <strk(at)keybit(dot)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(at)commandprompt(dot)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(at)keybit(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)keybit(dot)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(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)keybit(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)cybertec(dot)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(at)cybertec(dot)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(at)cybertec(dot)at> wrote:
> Merlin Moncure írta:
>> On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb(at)cybertec(dot)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(at)dunslane(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Andrew Dunstan <andrew(at)dunslane(dot)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(at)gmail(dot)com> writes:
> On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)dunslane(dot)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
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

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> writes:
> On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)phlo(dot)org(at)gmail(dot)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(dot)phlo(dot)org(at)gmail(dot)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