Re: triggers and execute...

On Tue, Apr 28, 2009 at 10:46 PM, David Fetter <david(at)fetter(dot)org> wrote:
> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>> <richard(dot)broersma(at)gmail(dot)com> wrote:
>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>> >>
>> >> create or replace function page_access_insert_trigger ()
>> >> returns trigger as $$
>> >> DECLARE
>> >>        part text;
>> >>        q text;
>> >> BEGIN
>> >>        part = to_char(new."timestamp",'YYYYMMDD');
>> >>        q = 'insert into page_access_'||part||' values (new.*)';
>> >> ...
>> >>
>> >> When I create it and try to use it I get this error:
>> >> ERROR:  NEW used in query that is not in a rule
>> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>> >
>> > At this point I don't think that there is a way for this function to
>> > know the correct table type of new.* since page_access_... is still
>> > only a concatenated string.  There there a way to cast new.* to the
>> > correct table type as part of this insert statement?
>>
>> 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.
>
> Well, you can add in piles of COALESCE, but that way madness lies.
>
> Instead, use dollar quoting, the appropriate quote_*() functions, and
> this:
>
> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Thanks so much! I'm off to read up on it. Dollar quoting, quote()
and the wiki. Thanks again.