Edit Question - KnowledgeHub

Title Body

I have a number of tables that use the Postgres "Partitioning" feature. I want to define a common BEFORE INSERT OF ROW trigger on each table that will 1) dynamically create the partition should the insert occur against the parent table and 2) re-execute the insert against the partition. Something like: CREATE OR REPLACE FUNCTION partition_insert_redirect( ) RETURNS trigger AS $BODY$ BEGIN ... create the new partition and set up the redirect Rules ... /* Redo the INSERT dynamically. The new RULE will redirect it to the child table */ EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) || ' SELECT NEW.*' END But the "NEW" record isn't visible inside the EXECUTE SQL. How can I make this work as simply as possible? As an alternative, can I iterate over the fields in the NEW record somehow? I've thought of using a temp-table: EXECUTE 'CREATE TEMPORARY TABLE new_row (LIKE ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) || ') ON COMMIT DROP'; INSERT INTO new_row SELECT NEW.*; EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) || ' SELECT * FROM new_row'; DROP TABLE new_row; But this also doesn't work because of the cached reference to a temp-table: Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions? I'm using Postgres 8.2 and I can't change to any other version. EDIT: As @alvherre pointed out, this can probably be done in Postgres 8.4 with the EXECUTE ... USING syntax. See an example at <a href="http://wiki.postgresql.org/wiki/PL/pgSQL_

Tags (comma-separated) Cancel