Re: dynamic insert in plpgsql

[Grzegorz Jaśkiewicz <gryzman@xxxxxxxxx>]

This is what I hacked quickly last night, what you guys think?

CREATE OR REPLACE FUNCTION something.ziew_partition_insert() RETURNS TRIGGER AS
$_$
DECLARE
  partition_table_name varchar;
  old_partition_table_name varchar;
BEGIN

  SELECT 'something_partitions.ziew_'||to_char(NEW.logtime, 'IYYY_MM')
INTO partition_table_name;

  BEGIN

   EXECUTE 'INSERT INTO '||partition_table_name||'  (SELECT
(something.ziew '||quote_literal(NEW)||').*)';

    EXCEPTION
      WHEN undefined_table THEN
        BEGIN
          SET client_min_messages = error;

          EXECUTE 'CREATE TABLE '||partition_table_name||'() INHERITS
(something.ziew)';
          EXECUTE 'ALTER TABLE  '||partition_table_name||' ADD PRIMARY
KEY (id)';
          EXECUTE 'CREATE INDEX something_time'|| to_char(NEW.logtime,
'IYYY_MM')||' ON '||partition_table_name||'(logtime)';

          SELECT 'something_partitions.ziew_'||to_char(NEW.logtime-'2
months'::interval, 'IYYY_MM') INTO old_partition_table_name;
          -- don't care if it fails
          BEGIN
            EXECUTE 'DROP TABLE '||old_partition_table_name;
            EXCEPTION
              WHEN others THEN
                --- in place for NOP
                old_partition_table_name := '';
          END;

          EXECUTE 'INSERT INTO '||partition_table_name||'  (SELECT
(something.ziew '||quote_literal(NEW)||').*)';
        EXCEPTION
          WHEN others THEN
            RAISE EXCEPTION 'somethings wrong %',SQLERRM;
            RETURN NULL;
        END;
    END;

  RETURN NULL;
END;
$_$ LANGUAGE 'plpgsql';

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general