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

On Wed, Aug 3, 2011 at 4:19 PM, James Robinson <jlrobins(at)socialserve(dot)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