Re: trigger failed to identify the partions

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Richard Huxton
Sent: Wednesday, September 09, 2009 3:35 PM
To: Sridhar Reddy Ratna
Cc: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)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(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
______________________________________________________________________________