Thread: Postgres / plpgsql equivalent to python's getattr() ?
Hackers,
Python's getattr() allows for dynamic lookup of attributes on an
object, as in:
inst = MyClass(x=12, y=24)v = getattr(inst, 'x')assert v == 12
Oftentimes in writing data validating trigger functions, it'd be real
handy to be able to do a similar thing in plpgsql against column
values in a row or record type, such as making use of a trigger
argument for hint as what column to consider in this table's case. Oh,
to be able to do something like (toy example known to be equivalent to
a check):
CREATE OR REPLACE FUNCTION must_be_positive() RETURNS TRIGGER AS$$begin if getattr(NEW, TG_ARGV[0]) <= 0 then
raise exception(TG_ARGV[0] || ' must be positive'); end if;
-- after trigger return null;end;$$ LANGUAGE PLPGSQL;
A function which takes a row + a text column name, and / or a peer
function taking row + index within row would really open up plpgsql's
expressivity in cases where you're writing mainly SQL stuff, not
really wanting to go over to plpythonu or whatnot (whose description
of rows are as dicts).
Is there something in the internals which inherently prevent this? Or
am I fool and it already exists?
Not having to defer to EXECUTE would be attractive.
----
James Robinson
Socialserve.com
James Robinson <jlrobins@socialserve.com> writes:
> Python's getattr() allows for dynamic lookup of attributes on an
> object, as in:
> inst = MyClass(x=12, y=24)
> v = getattr(inst, 'x')
> assert v == 12
> Oftentimes in writing data validating trigger functions, it'd be real
> handy to be able to do a similar thing in plpgsql
> Is there something in the internals which inherently prevent this?
plpgsql is strongly typed (much more so than python, anyway).
regards, tom lane
On Aug4, 2011, at 00:48 , Tom Lane wrote: > James Robinson <jlrobins@socialserve.com> writes: >> Python's getattr() allows for dynamic lookup of attributes on an >> object, as in: > >> inst = MyClass(x=12, y=24) >> v = getattr(inst, 'x') >> assert v == 12 > >> Oftentimes in writing data validating trigger functions, it'd be real >> handy to be able to do a similar thing in plpgsql > >> Is there something in the internals which inherently prevent this? > > plpgsql is strongly typed (much more so than python, anyway). You've brought forth that argument against dynamic attribute lookup in the past, but I remain unconvinced. The fact that plpgsql is strongly (or, more precisely, statically) types doesn't really get in the way as long as you require the dynamic attribute accessor's return type to be determined at compile time (make that "prepare time" in the case of plpgsql). The fact that I was able to implement dynamic accessor functions without any change to postgres or plpgsql proves that IMHO. The only bit that slightly kludgy about this in my opinion is the return-type determining "defvalue" argument of fieldvalue(). But again, this has little to do with static vs. dynamic typing but rather with the limitations of our support for polymorphic functions. @OP: Here's my implementation of the feature you desire as a set of C-language functions: https://github.com/fgp/pg_record_inspect. Other people did code up similar things in the past, but I currently cannot find any links to their work. But it little bit digging in the mailing list archives should turn them up. best regards, Florian Pflug
2011/8/4 Florian Pflug <fgp@phlo.org>: > On Aug4, 2011, at 00:48 , Tom Lane wrote: >> James Robinson <jlrobins@socialserve.com> writes: >>> Python's getattr() allows for dynamic lookup of attributes on an >>> object, as in: >> >>> inst = MyClass(x=12, y=24) >>> v = getattr(inst, 'x') >>> assert v == 12 >> >>> Oftentimes in writing data validating trigger functions, it'd be real >>> handy to be able to do a similar thing in plpgsql >> >>> Is there something in the internals which inherently prevent this? >> >> plpgsql is strongly typed (much more so than python, anyway). > > You've brought forth that argument against dynamic attribute lookup > in the past, but I remain unconvinced. The fact that plpgsql is strongly > (or, more precisely, statically) types doesn't really get in the way as > long as you require the dynamic attribute accessor's return type to be > determined at compile time (make that "prepare time" in the case of > plpgsql). > > The fact that I was able to implement dynamic accessor functions without > any change to postgres or plpgsql proves that IMHO. The only bit that slightly > kludgy about this in my opinion is the return-type determining "defvalue" > argument of fieldvalue(). But again, this has little to do with static vs. > dynamic typing but rather with the limitations of our support for polymorphic > functions. > > @OP: Here's my implementation of the feature you desire as a set of C-language > functions: https://github.com/fgp/pg_record_inspect. Other people did code up > similar things in the past, but I currently cannot find any links to their work. > But it little bit digging in the mailing list archives should turn them up. > it is pltoolbox http://www.pgsql.cz/index.php/PL_toolbox_%28en%29 regards Pavel > best regards, > Florian Pflug > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Wed, 2011-08-03 at 18:48 -0400, Tom Lane wrote: > James Robinson <jlrobins@socialserve.com> writes: > > Python's getattr() allows for dynamic lookup of attributes on an > > object, as in: > > > inst = MyClass(x=12, y=24) > > v = getattr(inst, 'x') > > assert v == 12 > > > Oftentimes in writing data validating trigger functions, it'd be real > > handy to be able to do a similar thing in plpgsql > > > Is there something in the internals which inherently prevent this? > > plpgsql is strongly typed (much more so than python, anyway). For example the plpgsql type RECORD is about as strongly typed as (some) python classes , that is once assigned the record itself is typed, but the same variable can point to different record types -- ------- Hannu Krosing PostgreSQL Infinite Scalability and Performance Consultant PG Admin Book: http://www.2ndQuadrant.com/books/
On Aug 4, 2011, at 4:55 AM, Florian Pflug wrote: .... > > @OP: Here's my implementation of the feature you desire as a set of > C-language > functions: https://github.com/fgp/pg_record_inspect. Other people > did code up > similar things in the past, but I currently cannot find any links to > their work. > But it little bit digging in the mailing list archives should turn > them up. Many thanks, Florian, we'll be checking that out. James ---- James Robinson Socialserve.com
On Wed, Aug 3, 2011 at 4:19 PM, James Robinson <jlrobins@socialserve.com> wrote: > Hackers, > > Python's getattr() allows for dynamic lookup of attributes on an object, as > in: > > inst = MyClass(x=12, y=24) > v = getattr(inst, 'x') > assert v == 12 > > Oftentimes in writing data validating trigger functions, it'd be real handy > to be able to do a similar thing in plpgsql against column values in a row > or record type, such as making use of a trigger argument for hint as what > column to consider in this table's case. Oh, to be able to do something like > (toy example known to be equivalent to a check): > > CREATE OR REPLACE FUNCTION must_be_positive() RETURNS TRIGGER AS > $$ > begin > if getattr(NEW, TG_ARGV[0]) <= 0 > then > raise exception(TG_ARGV[0] || ' must be positive'); > end if; > > -- after trigger > return null; > end; > $$ LANGUAGE PLPGSQL; > > > A function which takes a row + a text column name, and / or a peer function > taking row + index within row would really open up plpgsql's expressivity in > cases where you're writing mainly SQL stuff, not really wanting to go over > to plpythonu or whatnot (whose description of rows are as dicts). > > Is there something in the internals which inherently prevent this? Or am I > fool and it already exists? > > Not having to defer to EXECUTE would be attractive. Aside from the other great solutions mentioned, you can run a record type through hstore and pull fields dynamically that way. The hstore method is a variant of the general 'coerce everything to text' strategy. Florian's approach is likely faster, but more verbose? merlin