From: Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
To: 'Richard Huxton' <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger failed to identify the partions
Date: Wed, 9 Sep 2009 16:35:42 +0530
Message-ID: <D61F8ACA9D3743EEAB0538006FA211DC@Sridharvisic> (raw)
In-Reply-To: <4AA77DD7.5040908@archonet.com>
References: <17DD0201A12947039313F26974E8E9B4@Sridharvisic>
<4AA77DD7.5040908@archonet.com>
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
______________________________________________________________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sridhar.ratna@cmcltd.com, dev@archonet.com
Subject: Re: trigger failed to identify the partions
In-Reply-To: <D61F8ACA9D3743EEAB0538006FA211DC@Sridharvisic>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox