On Tue, Apr 28, 2009 at 10:46 PM, David Fetter <david@xxxxxxxxxx> 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.broersma@xxxxxxxxx> wrote: >> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
