Re: triggers and execute...

On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote:

> Oh man, it just gets worse.  I really need a simple elegant solution
> here, because if I try to build the query by hand null inputs make
> life a nightmare.  I had built something like this:
>
> q = 'insert into '||schem||'.page_access_'||part||' values (
>                '||new.paid||',
>                '''||new.timestamp||''',
>                '||new.total_time||',
>                '''||new.http_host||''',
>                '''||new.php_self||''',
>                '''||new.query_string||''',
>                '''||new.remote_addr||''',
>                '''||new.logged_in||''',
>                '||new.uid||',
>                '''||new.http_user_agent||''',
>                '''||new.server_addr||''',
>                '''||new.notes||'''
>        )';
>        execute q;
>
> But if any of the fields referenced are null, the whole query string
> is now null.  So the next step is to use coalesce to build a query
> string?  That get insane very quickly.  There's got to be some quoting
> trick or something to let me use new.*, please someone see this and
> know what that trick is.


I think you could do this if you'd be using a PL-language that
supported reflection (on the NEW objects' type in this case). I can't
say I know which one does though, I've only been using PL/pgsql so
far, but I'd guess PL/Python, PL/Perl or PL/Java should be able to do
the trick. Or plain C.

AFAIK there's no way to dynamically list column names from a table-
type variable like NEW in PL/pgsql, which is why the above probably
can't be done any easier using PL/pgsql. It would be nice to be able
to LOOP over a variable like that or some similar method (I guess a
more relational approach where the columns would be available as a
result set would be preferred), especially if it'd be similarly easy
to inspect the name and type of each column.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49f82a8c129742043099112!