Dyamic updates of NEW with pl/pgsql

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

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

Any workaround you may suggest ?

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

2010/3/9 strk <strk@keybit.net>:

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

Any workaround you may suggest ?

I don't know it - use C language maybe.

Pavel

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

well, it's possible. it's just not nice.

http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

hubert depesz lubaczewski wrote:

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

well, it's possible. it's just not nice.

http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/

Using an hstore in 9.0 it's not too bad, Try something like:

CREATE OR REPLACE FUNCTION dyntrig()
RETURNS trigger
LANGUAGE plpgsql
AS $function$

declare
hst hstore;
begin
hst := hstore(NEW);
hst := hst || ('foo' => 'bar');
NEW := populate_record(NEW,hst);
return NEW;
end;

$function$;

But this question probably belongs on -general rather than -hackers.

cheers

andrew

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

It's not possible in plpgsql, but you can write plperl function, and later use
it in plpgsql triggers.

Regards,
Dmitry

On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote:

Using an hstore in 9.0 it's not too bad,

Does it still have a limit of 65535 bytes per field ?

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

On Thu, Mar 11, 2010 at 03:27:23PM +0100, strk wrote:

On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote:

Using an hstore in 9.0 it's not too bad,

Does it still have a limit of 65535 bytes per field ?

No. :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Wed, Mar 10, 2010 at 7:50 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

Using an hstore in 9.0 it's not too bad, Try something like:

Agree 100%. The new hstore going to completely nail a broad class of
issues that have historically been awkward in plpgsql functions.
(small aside: the other biggie would be able to push a composite type
in to an update statement...something like 'update foo set foo =
new'). This is really great...some variant of this question is
continually asked it seems.

merlin

Merlin Moncure escribi�:

(small aside: the other biggie would be able to push a composite type
in to an update statement...something like 'update foo set foo =
new'). This is really great...some variant of this question is
continually asked it seems.

Can't you already do that with EXECUTE ... USING NEW? hmm, ah, but you
have to specify the columns in NEW, so it doesn't really work for you,
does it?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Mar 11, 2010 at 11:24 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Merlin Moncure escribió:

(small aside: the other biggie would be able to push a composite type
in to an update statement...something like 'update foo set foo =
new').  This is really great...some variant of this question is
continually asked it seems.

Can't you already do that with EXECUTE ... USING NEW?  hmm, ah, but you
have to specify the columns in NEW, so it doesn't really work for you,
does it?

right...with inserts you can expand the composite type without listing
the columns. updates can't do it because of syntax issues, even if
you go dynamic.

merlin

On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote:

hubert depesz lubaczewski wrote:

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

well, it's possible. it's just not nice.

http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/

Using an hstore in 9.0 it's not too bad, Try something like:

CREATE OR REPLACE FUNCTION dyntrig()
RETURNS trigger
LANGUAGE plpgsql
AS $function$

declare
hst hstore;
begin
hst := hstore(NEW);
hst := hst || ('foo' => 'bar');
NEW := populate_record(NEW,hst);
return NEW;
end;

$function$;

But this question probably belongs on -general rather than -hackers.

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2010/3/12 David Fetter <david@fetter.org>:

On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote:

hubert depesz lubaczewski wrote:

On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote:

2010/3/9 strk <strk@keybit.net>:

How can a pl/pgsql trigger change the
values of dynamic fields in NEW record ?

By "dynamic" I mean that the field name
is a variable in the trigger context.

I've been told it's easy to do with pl/perl but
I'd like to delive a pl/pgsql solution to have
less dependencies.

It isn't possible yet

well, it's possible. it's just not nice.

http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/

Using an hstore in 9.0 it's not too bad, Try something like:

   CREATE OR REPLACE FUNCTION dyntrig()
    RETURNS trigger
    LANGUAGE plpgsql
   AS $function$

   declare
           hst hstore;
   begin
           hst := hstore(NEW);
           hst := hst || ('foo' => 'bar');
           NEW := populate_record(NEW,hst);
           return NEW;
   end;

   $function$;

But this question probably belongs on -general rather than -hackers.

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

I like it - but it looking little bit strange - I thinking we need
only one function (maybe with some special support from pl executor)

begin
update_field(NEW, 'field', value);
....

Pavel

On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote:

2010/3/12 David Fetter <david@fetter.org>:

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

I like it - but it looking little bit strange - I thinking we need
only one function (maybe with some special support from pl executor)

begin
update_field(NEW, 'field', value);
....

This doesn't seem like a terribly useful addition, it being specific
to PL/pgsql. Then there's the quoting issue, which the above doesn't
quite address. Putting hstore in would let all the other PLs use it,
to the extent that they need such a thing. :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, Mar 12, 2010 at 10:47:45AM -0800, David Fetter wrote:

On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote:

2010/3/12 David Fetter <david@fetter.org>:

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

I like it - but it looking little bit strange - I thinking we need
only one function (maybe with some special support from pl executor)

begin
update_field(NEW, 'field', value);
....

This doesn't seem like a terribly useful addition, it being specific
to PL/pgsql. Then there's the quoting issue, which the above doesn't
quite address. Putting hstore in would let all the other PLs use it,
to the extent that they need such a thing. :)

Plus pure SQL use !
I was considering using hstore for a table value too for
a form of "historic table". Just to say I'd also be happy with
it being core in pgsql :)

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

2010/3/12 strk <strk@keybit.net>:

On Fri, Mar 12, 2010 at 10:47:45AM -0800, David Fetter wrote:

On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote:

2010/3/12 David Fetter <david@fetter.org>:

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

I like it - but it looking little bit strange - I thinking we need
only one function (maybe with some special support from pl executor)

begin
  update_field(NEW, 'field', value);
  ....

This doesn't seem like a terribly useful addition, it being specific
to PL/pgsql.  Then there's the quoting issue, which the above doesn't
quite address.  Putting hstore in would let all the other PLs use it,
to the extent that they need such a thing. :)

Plus pure SQL use !
I was considering using hstore for a table value too for
a form of "historic table". Just to say I'd also be happy with
it being core in pgsql :)

I see some disadvantages

a) non intuitive name - hstore is very specific name
b) effectivity (mainly inside trigger body) - plpgsql specific
construct can be 10x faster.

I would to see hash tables in core too, but I don't think so it is
good solution for record updating.

Regards
Pavel

strk �rta:

On Fri, Mar 12, 2010 at 10:47:45AM -0800, David Fetter wrote:

On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote:

2010/3/12 David Fetter <david@fetter.org>:

This is, by the way, an excellent argument for including hstore in
core in 9.1. :)

I like it - but it looking little bit strange - I thinking we need
only one function (maybe with some special support from pl executor)

begin
update_field(NEW, 'field', value);
....

This doesn't seem like a terribly useful addition, it being specific
to PL/pgsql. Then there's the quoting issue, which the above doesn't
quite address. Putting hstore in would let all the other PLs use it,
to the extent that they need such a thing. :)

Plus pure SQL use !

What's wrong with "UPDATE foo SET (foo) = (NEW);" ?

I know it's a little ambiguous, as table "foo" can have fields
named "foo" and "new", but the
UPDATE foo SET (field, ...) = (value, ...);
works in plain SQL and the (...) usually denotes a list with
more than one field/value. pl/pgSQL could treat the
"list with single name" as a special case (maybe checking
whether the table has fields "foo", "new" and/or "old" and
issue a warning when relevant) and treat the above as a
whole-row update.

Best regards,
Zolt�n B�sz�rm�nyi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:

What's wrong with "UPDATE foo SET (foo) = (NEW);" ?

amen brother! :-)

I say though, since you can do:
SELECT foo FROM foo;
why not
UPDATE foo SET foo = new;?

merlin

Merlin Moncure �rta:

On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:

What's wrong with "UPDATE foo SET (foo) = (NEW);" ?

amen brother! :-)

I say though, since you can do:
SELECT foo FROM foo;
why not
UPDATE foo SET foo = new;?

I just tried this:

zozo=# create table foo (foo integer, bar integer);
CREATE TABLE
zozo=# insert into foo values (1, 2), (2, 4);
INSERT 0 2
zozo=# select foo from foo;
foo
-----
1
2
(2 rows)

zozo=# create table foo1 (foo integer, bar integer);
CREATE TABLE
zozo=# insert into foo1 values (1, 2), (2, 4);
INSERT 0 2
zozo=# select foo1 from foo1;
foo1
-------
(1,2)
(2,4)
(2 rows)

So, if the table has field that's name is the same as the table name
then SELECT foo FROM foo; returns the field, not the whole row,
it's some kind of a precedence handling. What we could do is the
reverse precedence with
UPDATE foo SET foo = 3 WHERE foo = 1;
vs
UPDATE foo SET (foo) = (1,3) WHERE (foo) = (1,2);

Note the WHERE condition, I would expect it to work there, too.
If it works in plain SQL then no special casing would be needed
in PLs.

Best regards,
Zolt�n B�sz�rm�nyi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/