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 netsupp...@cmcltd.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
______________________________________________________________________________
