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!