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