Re: triggers and execute...

[Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>]

On Wed, Apr 29, 2009 at 4:23 AM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> 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.

I'm really close to using coalesce to make this work, since I can't
get the referenced at
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers to work.  If
there was some simple quoting trick to get the original (NEW.*) stuff
to work I'd be gold.

Either that or just implement this all in rules with a simple cron job
that creates the new table as needed a week or so in advnace.