Re: triggers and execute...

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

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.