Thread: wishlist for 8.4
hi there, I recently found myself trying to build a trigger to modify some fields in a good dozen similarly structured tables in which the similar columns had different names. in fact, I got stuck in pl/pgsql with the fact that there's no way to access the NEW tuple in an indirect way, having the name of the column in some variable. (I found that it could be done in plperl, but that left me with a taste of un-completeness...) so, I propose the use ofNEW[variable_containing_the_column_name] (which can obviously be extended to any tuples) to allow such access. what do you experts think ?
On Thu, Feb 14, 2008 at 07:51:31AM +0100, Raphaël Jacquot wrote: > in fact, I got stuck in pl/pgsql with the fact that there's no way to > access the NEW tuple in an indirect way, having the name of the column > in some variable. (I found that it could be done in plperl, but that > left me with a taste of un-completeness...) > > so, I propose the use of > > NEW[variable_containing_the_column_name] It's not that simple. pl/pgsql is a statically typed language. Which means the types of the variable aren't really allowed to change between executions. The problem with your construct is that you can't tell a priori what its type is. The problem stems (I beleive) from the postgresql parser itself, since SQL doesn't have the structure you want either, and pl/pgsql uses the SQL parser for all the hard work. The reason why other languages can do it is because they are typed at run-time, not compile time. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Raphaël Jacquot <sxpert@sxpert.org> writes:
> so, I propose the use of
> NEW[variable_containing_the_column_name]
> (which can obviously be extended to any tuples)
> to allow such access.
> what do you experts think ?
Zero chance. plplgsql is a strongly typed language, and a construct
like that couldn't have any known-in-advance data type.
regards, tom lane
On Feb 14, 2008, at 10:06 AM, Tom Lane wrote: > Raphaël Jacquot <sxpert@sxpert.org> writes: >> so, I propose the use of >> NEW[variable_containing_the_column_name] >> (which can obviously be extended to any tuples) >> to allow such access. > >> what do you experts think ? > > Zero chance. plplgsql is a strongly typed language, and a construct > like that couldn't have any known-in-advance data type. Would it be reasonable to teach EXECUTE about NEW and OLD? That should allow the OP to do what he's looking for... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Decibel! wrote: > On Feb 14, 2008, at 10:06 AM, Tom Lane wrote: >> Raphaël Jacquot <sxpert@sxpert.org> writes: >>> so, I propose the use of >>> NEW[variable_containing_the_column_name] >>> (which can obviously be extended to any tuples) >>> to allow such access. >> >>> what do you experts think ? >> >> Zero chance. plplgsql is a strongly typed language, and a construct >> like that couldn't have any known-in-advance data type. > > Would it be reasonable to teach EXECUTE about NEW and OLD? That should > allow the OP to do what he's looking for... You could have a function get_attribute_as_text(NEW, 'id') or even get_attribute_quoted(NEW, 'id') It would be nice to have a more dynamic language built-in. I'm not aware of any BSD-licensed dynamic languages though. -- Richard Huxton Archonet Ltd
On Thu, Feb 14, 2008 at 11:06:42AM -0500, Tom Lane wrote:
> Raphaël Jacquot <sxpert@sxpert.org> writes:
> > so, I propose the use of
> > NEW[variable_containing_the_column_name]
> > (which can obviously be extended to any tuples)
> > to allow such access.
>
> > what do you experts think ?
>
> Zero chance. plplgsql is a strongly typed language, and a construct
> like that couldn't have any known-in-advance data type.
If "variable_containing_the_column_name" was a string then I agree,
if columns were first class objects then it wouldn't be a problem.
Introducing sub-types would make this all nice and tidy, but
unfortunately sub-types are a long way away from what PG currently
supports and I don't know the SQL spec well enough to know if there
would be any serious incompatibilities with them--they are pretty
expressive so I'd doubt there would be any fundamental incompatibility.
If you want an example, Java has sub-types and 1.5+ has parametric
polymorphism making it possible to create an interface that named the
column you were interested in and then to write a function like:
interface Column<Row,ColType> { public ColType get(Row r); } public <Row,ColType> ColType foo(Row row,
Column<Row,ColType>col) { return col.get(row); }
In PG you'd want the sub-types to be generated automatically, the above
example is somewhat unreadable. The only real purpose of the above was
to show how a strong type system can express what the OP wanted.
The anyelement and supporting polymorphism would all be subsumed into
the features provided by sub-typing and would all be done in a much
tidier way.
It's a very big change for PG though!
Sam
Raphaël Jacquot wrote:
> I recently found myself trying to build a trigger to modify some fields
> in a good dozen similarly structured tables in which the similar columns
> had different names.
> in fact, I got stuck in pl/pgsql with the fact that there's no way to
> access the NEW tuple in an indirect way, having the name of the column
> in some variable. (I found that it could be done in plperl, but that
> left me with a taste of un-completeness...)
It's ugly, but you could play tricks with EXECUTE. Like:
CREATE OR REPLACE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
DECLARE empname text;
BEGIN EXECUTE 'SELECT ('''||new||'''::emp).empname' INTO empname; RAISE NOTICE 'new empname is: %', empname;
RETURNNEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
Not sure the quoting is right...
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Fri, Feb 15, 2008 at 10:06:49AM +0000, Richard Huxton wrote: > Decibel! wrote: >> On Feb 14, 2008, at 10:06 AM, Tom Lane wrote: >>> Raphaël Jacquot <sxpert@sxpert.org> writes: >>>> so, I propose the use of >>>> NEW[variable_containing_the_column_name] >>>> (which can obviously be extended to any tuples) to allow such >>>> access. >>> >>>> what do you experts think ? >>> >>> Zero chance. plplgsql is a strongly typed language, and a >>> construct like that couldn't have any known-in-advance data type. >> >> Would it be reasonable to teach EXECUTE about NEW and OLD? That >> should allow the OP to do what he's looking for... > > You could have a function get_attribute_as_text(NEW, 'id') or even > get_attribute_quoted(NEW, 'id') > > It would be nice to have a more dynamic language built-in. I'm not > aware of any BSD-licensed dynamic languages though. Perl is BSD-compatible. 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 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Richard Huxton wrote: > It would be nice to have a more dynamic language built-in. I'm not > aware of any BSD-licensed dynamic languages though. > Lua is MIT. And I believe there's even a pl/Lua around. The small size of the interpreter and ease of C interfacing would make it ideal, or at least it seems that way to me.