Re: triggers and execute... - Mailing list pgsql-general

From David Fetter
Subject Re: triggers and execute...
Date
Msg-id 20090429044657.GC27925@fetter.org
Whole thread Raw
In response to Re: triggers and execute...  (Scott Marlowe <scott.marlowe@gmail.com>)
Responses Re: triggers and execute...
List pgsql-general
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.broersma@gmail.com> wrote:
> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.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

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

pgsql-general by date:

Previous
From: Peter Geoghegan
Date:
Subject: Connecting to a postgreSQL database with windows CE over wi-fi; failing gracefully
Next
From: Alan Hodgson
Date:
Subject: Re: pg_dump and pg_restore problem