On Apr 29, 2009, at 4:14 AM, Jasen Betts wrote:
On 2009-04-29, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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:
sounds like you want quote_literal() and/or coalesce()
EXECUTE 'insert into '|| quote_ident($1) || ' (data) values (' ||
coalesce(quote_literal( $2 ),'NULL');
I'm fairly certain that quote_literal doesn't work with null values,
hence the inclusion of quote_nullable() in 8.4. I ran into this same
issue when working on a pet project called pg_partitioner (http://github.com/mage2k/pg_partitioner/tree/master
). Since 8.4 obviously wasn't available for that yet I ended writing
my own quote_nullable(), pretty simple.
Scott,
I also couldn't come up with a dynamic way to use new.* so I ended up
just writing out the attribute names in my partition triggers, as
well. In fact, you may want to take a look at pg_partitioner. It
needs some polish but most of the basic functionality is there.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
