Обсуждение: trigger failed to identify the partions

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

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