Re: trigger failed to identify the partions - Mailing list pgsql-sql

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