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 of
NEW[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/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
=?UTF-8?B?UmFwaGHDq2wgSmFjcXVvdA==?= <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:
=?UTF-8?B?UmFwaGHDq2wgSmFjcXVvdA==?= <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
Attachments:
Decibel! wrote:
On Feb 14, 2008, at 10:06 AM, Tom Lane wrote:
=?UTF-8?B?UmFwaGHDq2wgSmFjcXVvdA==?= <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:
=?UTF-8?B?UmFwaGHDq2wgSmFjcXVvdA==?= <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;
RETURN NEW;
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:
=?UTF-8?B?UmFwaGHDq2wgSmFjcXVvdA==?= <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.