Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 631C36358ED for ; Wed, 9 Sep 2009 06:33:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41116-09 for ; Wed, 9 Sep 2009 09:32:53 +0000 (UTC) X-Greylist: delayed 00:12:11.649179 by SQLgrey-1.7.6 Received: from mx1.securemx.in (mx1.securemx.in [121.241.247.20]) by mail.postgresql.org (Postfix) with ESMTP id 21ABA633557 for ; Wed, 9 Sep 2009 06:32:50 -0300 (ADT) Received: from oedge102-rme.smx.co.in ([10.41.20.27]) by omta102-rme.smx.co.in with ESMTP id <20090909092035.EPZP31362.omta102-rme.smx.co.in@oedge102-rme.smx.co.in> for ; Wed, 9 Sep 2009 09:20:35 +0000 Received: from smtp.cmcltd.com ([59.160.161.10]) by oedge102-rme.smx.co.in with ESMTP id <20090909092034.BLFU8535.oedge102-rme.smx.co.in@smtp.cmcltd.com> for ; Wed, 9 Sep 2009 09:20:34 +0000 Received: from Sridharvisic ([172.16.14.247]) by smtp.cmcltd.com (8.13.1/8.13.1) with ESMTP id n899NEGU020325 for ; Wed, 9 Sep 2009 14:53:18 +0530 From: "Sridhar Reddy Ratna" To: Subject: trigger failed to identify the partions Date: Wed, 9 Sep 2009 14:56:51 +0530 Message-ID: <17DD0201A12947039313F26974E8E9B4@Sridharvisic> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000A_01CA315D.C3DD24D0" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 thread-index: AcoxL6nagdGIIWLfQQGKbZ3rsgwMLg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.957 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001, ROUND_THE_WORLD_LOCAL=2.696 X-Spam-Level: * X-Archive-Number: 200909/33 X-Sequence-Number: 33098 This is a multi-part message in MIME format. ------=_NextPart_000_000A_01CA315D.C3DD24D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@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 ______________________________________________________________________________ ------=_NextPart_000_000A_01CA315D.C3DD24D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

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 PRIMAR= Y KEY (rrid)

)

WITH (OIDS=3DFALSE);

 

CREATE TABLE coll_fp_subdtls_01=

(

  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (r= rid)

)

INHERITS (coll_fp_submission_details)<= /font>

WITH (OIDS=3DFALSE)

TABLESPACE fpsdts01;

 

CREATE TABLE coll_fp_subdtls_02=

(

  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (r= rid)

)

INHERITS (coll_fp_submission_details)<= /font>

WITH (OIDS=3DFALSE)

TABLESPACE fpsdts02;

 

 

 

Now created a trigger as below

 

CREATE OR REPLACE FUNCTION ins_submission_details()=

        RETURNS TRIGG= ER AS $$

          D= ECLARE

          d= ateTable TEXT;

          c= md TEXT;

        BEGIN

 

         &n= bsp;  IF ((NEW.rrid % 2)=3D 0) THEN

         &n= bsp;            = ;  dateTable :=3D coll_fp_subdtls_01;

         &n= bsp;  ELSE

         &n= bsp;            = ;  dateTable :=3D coll_fp_subdtls_02;

 

         &n= bsp;  END IF;

 

         &n= bsp;  cmd :=3D 'INSERT INTO ' || dateTable  || '(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||=

         &n= bsp;      ' VALUES (' ||  quote_ident(NEW.rri= d) || ',' ||

         &n= bsp;            = ;      quote_ident(NEW.sid) || ',' ||

         &n= bsp;            = ;      quote_ident(NEW.pfid) || ',' ||<= /span>

         &n= bsp;            = ;      quote_literal(NEW.timestamp) || ',' ||

         &n= bsp;            = ;      quote_ident(NEW.schema_version) || ',' ||

         &n= bsp;            = ;      quote_literal(NEW.details) || ',' ||

         &n= bsp;            = ;      quote_literal(NEW.app_txn_id) || ',';

 

         &n= bsp;  EXECUTE cmd;

         &n= bsp;  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();<= /o:p>

 

 

 

Now I am trying to insert data into tab= le

 

INSERT INTO coll_fp_submission_details( rrid, sid, pfid, "timestamp", schema_version, details, app_txn_id)  &nbs= p; 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

         &n= bsp;      ^

QUERY:  SELECT  coll_fp_subdtls_01<= /span>

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

 

 

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

 

ERROR: column "coll_fp_subdtls_01" does not ex= ist

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 th= e 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 intende= d recipient, any dissemination, use, review, distribution, printing or copyi= ng 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@cmcltd.com or tele= phone and immediately and permanently delete the message and any attachments. Th= ank you.
This email has been scrubbed for your protection by SecureMX. For more information visit securemx.in
------=_NextPart_000_000A_01CA315D.C3DD24D0--