Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A31B06381E5 for ; Wed, 9 Sep 2009 08:00:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 39151-02 for ; Wed, 9 Sep 2009 10:59:43 +0000 (UTC) X-Greylist: from auto-whitelisted 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 787076381E2 for ; Wed, 9 Sep 2009 07:59:46 -0300 (ADT) Received: from oedge102-rme.smx.co.in ([10.41.20.27]) by omta102-rme.smx.co.in with ESMTP id <20090909105943.ETWA31362.omta102-rme.smx.co.in@oedge102-rme.smx.co.in>; Wed, 9 Sep 2009 10:59:43 +0000 Received: from smtp.cmcltd.com ([59.160.161.10]) by oedge102-rme.smx.co.in with ESMTP id <20090909105934.BMXC8535.oedge102-rme.smx.co.in@smtp.cmcltd.com>; Wed, 9 Sep 2009 10:59:34 +0000 Received: from Sridharvisic ([172.16.14.247]) by smtp.cmcltd.com (8.13.1/8.13.1) with ESMTP id n89B256V005507; Wed, 9 Sep 2009 16:32:14 +0530 From: "Sridhar Reddy Ratna" To: "'Richard Huxton'" Cc: References: <17DD0201A12947039313F26974E8E9B4@Sridharvisic> <4AA77DD7.5040908@archonet.com> Subject: Re: trigger failed to identify the partions Date: Wed, 9 Sep 2009 16:35:42 +0530 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0018_01CA316B.9635A440" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 thread-index: AcoxNPOwruHp7UV0QVGh9YJmAsEUcwABx00g In-Reply-To: <4AA77DD7.5040908@archonet.com> 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/35 X-Sequence-Number: 33100 This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CA316B.9635A440 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi Richard, Thanks for your suggestion. It worked great. But when I used table spaces for the inherited tables, data is being inserted to the fpsdts01 or fpsdts02 along with the default table space. So I am getting duplicate rows in select SQL. I have created the table with default table space 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; In the trigger 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_literal(NEW.rrid) || ',' || quote_literal(NEW.sid) || ',' || quote_literal(NEW.pfid) || ',' || quote_literal(NEW.timestamp) || ',' || quote_literal(NEW.schema_version) || ',' || quote_literal(NEW.details) || ',' || quote_literal(NEW.app_txn_id) || ')'; EXECUTE cmd; RETURN NEW; END; $$LANGUAGE 'plpgsql'; If I changed the RETURN NEW to RETURN NULL its inserting only one row. But to work with hibernate I need the return NEW statement. Please help me in resolving this. Thanks in advance, Sridhar ratna -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Richard Huxton Sent: Wednesday, September 09, 2009 3:35 PM To: Sridhar Reddy Ratna Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] trigger failed to identify the partions Sridhar Reddy Ratna wrote: > > dateTable := coll_fp_subdtls_01; > > ELSE > > dateTable := coll_fp_subdtls_02; > ERROR: column "coll_fp_subdtls_01" does not exist > > ERROR: column "coll_fp_subdtls_01" does not exist I think you missed the word "column" in the error message (easy to do, you know you are naming tables). You've missed the quotes around the partition-names so it's trying to find a column on a table that matches. dateTable := 'coll_fp_subdtls_01'; -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ______________________________________________________________________________ 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_0018_01CA316B.9635A440 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

Hi Richard,

 

Thanks for your suggestion. It worked great.

 

But when I used table spaces for the inherited tables, data is bein= g inserted to the fpsdts01 or fpsdts02 along with the default table space.

So I am getting duplicate rows in select SQL.

 

I have created the table with default table space 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=3DFALSE);

 

CREATE TABLE coll_fp_subdtls_01(

  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)

)INHERITS (coll_fp_submission_details)

WITH (OIDS=3DFALSE)

TABLESPACE fpsdts01;

 

CREATE TABLE coll_fp_subdtls_02(

  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)

)INHERITS (coll_fp_submission_details)

WITH (OIDS=3DFALSE)

TABLESPACE fpsdts02;

 

 

 

In the trigger

 

CREATE OR REPLACE FUNCTION ins_submission_details()

        RETURNS TRIGGER AS $$

          DECLARE=

          dateTable TE= XT;

          cmd TEXT;

        BEGIN<= /font>

 

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

            = dateTable :=3D 'coll_fp_subdtls_01';

      ELSE

            = dateTable :=3D 'coll_fp_subdtls_02';

 

      END IF;

 

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

           &= nbsp;    ' VALUES (' ||  quote_literal(NEW.rrid) || ',' ||

           &= nbsp;           &nbs= p;    quote_literal(NEW.sid) || ',' ||

           &= nbsp;                quote_literal(NEW.pfid) || ',' ||=

           &= nbsp;           &nbs= p;    quote_literal(NEW.timestamp) || ',' ||

           &= nbsp;           &nbs= p;    quote_literal(NEW.schema_version) || ',' ||

           &= nbsp;           &nbs= p;    quote_literal(NEW.details) || ',' ||

           &= nbsp;              &nb= sp; quote_literal(NEW.app_txn_id) || ')';

 

      EXECUTE cmd;

      RETURN NEW;

        END;

      $$LANGUAGE 'plpgsql';

 

 

If I changed the RETURN NEW to RETURN NULL its inserting only one row.

But to work with hibernate I need the return NEW statement.

 

 

Please help me in resolving this.

 

Thanks in advance,

Sridhar ratna

 

 

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org= ] On Behalf Of Richard Huxton
Sent: Wednesday, September 09, 2009 3:35 PM
To: Sridhar Reddy Ratna
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] trigger failed to identify the partions

 

Sridhar Reddy Ratna wrote:

>

>          &nb= sp;            =   dateTable :=3D coll_fp_subdtls_01;

>

>          &nb= sp;  ELSE

>

>          &nb= sp;            =   dateTable :=3D coll_fp_subdtls_02;

 

 

> ERROR:  column "coll_fp_subdtls_01" does not ex= ist

>

> ERROR: column "coll_fp_subdtls_01" does not exist

 

 

I think you missed the word "column" in the error message (easy to do,

you know you are naming tables). You've missed the quotes around th= e

partition-names so it's trying to find a column on a table that matches.

 

  dateTable :=3D 'coll_fp_subdtls_01';

 

 

--

  Richard Huxton

  Archonet Ltd

 

--

Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-sql

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_0018_01CA316B.9635A440--