trigger failed to identify the partions - Mailing list pgsql-sql

Hi all,

I have created a table and partitions as below.

CREATE TABLE coll_fp_submission_details

(

  rrid numeric NOT NULL,

  sid numeric NOT NULL,

  pfid numeric NOT NULL,

  "timestamp" date NOT NULL,

  schema_version numeric NOT NULL,

  details character varying NOT NULL,

  app_txn_id character varying NOT NULL,

  CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)

)

WITH (OIDS=FALSE);

CREATE TABLE coll_fp_subdtls_01

(

  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)

)

INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts01;

CREATE TABLE coll_fp_subdtls_02

(

  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)

)

INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts02;

Now created a trigger as below

CREATE OR REPLACE FUNCTION ins_submission_details()

        RETURNS TRIGGER AS $$

          DECLARE

          dateTable TEXT;

          cmd TEXT;

        BEGIN

            IF ((NEW.rrid % 2)= 0) THEN

                        dateTable := coll_fp_subdtls_01;

            ELSE

                        dateTable := coll_fp_subdtls_02;

            END IF;

            cmd := 'INSERT INTO ' || dateTable  || '(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||

                ' VALUES (' ||  quote_ident(NEW.rrid) || ',' ||

                            quote_ident(NEW.sid) || ',' ||

                            quote_ident(NEW.pfid) || ',' ||

                            quote_literal(NEW.timestamp) || ',' ||

                            quote_ident(NEW.schema_version) || ',' ||

                            quote_literal(NEW.details) || ',' ||

                            quote_literal(NEW.app_txn_id) || ',';

            EXECUTE cmd;

            RETURN NULL;

        END;

      $$LANGUAGE 'plpgsql';

CREATE TRIGGER trig_ins_submission_details

  BEFORE INSERT

  ON coll_fp_submission_details

  FOR EACH ROW

  EXECUTE PROCEDURE ins_submission_details();

Now I am trying to insert data into table

INSERT INTO coll_fp_submission_details( rrid, sid, pfid, "timestamp", schema_version, details, app_txn_id)    VALUES (102, 101, 101, '2009-09-09', 1,'dtls', '1234');

The error is

ERROR:  column "coll_fp_subdtls_01" does not exist

LINE 1: SELECT  coll_fp_subdtls_01

                ^

QUERY:  SELECT  coll_fp_subdtls_01

CONTEXT:  PL/pgSQL function "ins_submission_details" line 7 at assignment

********** Error **********

ERROR: column "coll_fp_subdtls_01" does not exist

SQL state: 42703

Context: PL/pgSQL function "ins_submission_details" line 7 at assignment

Can any body help me what is this problem and what is the solution.

Thanks in advance,

Sridhar Ratna