Postgres / plpgsql equivalent to python's getattr() ?

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

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