trigger failed to identify the partions

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

______________________________________________________________________________

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupport(at)cmcltd(dot)com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.

______________________________________________________________________________

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
______________________________________________________________________________