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

<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Hi Richard,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Thanks for your suggestion. It worked great.</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">But when I used table spaces for the inherited tables, data is being inserted to the fpsdts01 or fpsdts02 along
withthe default table space.</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">So I am getting duplicate rows in select SQL.</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">I have created the table with default table space as below</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE TABLE coll_fp_submission_details(</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">  rrid numeric NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  sid numeric NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  pfid numeric NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  "timestamp" date NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  schema_version numeric NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  details character varying NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">  app_txn_id character varying NOT NULL,</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">  CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">)WITH (OIDS=FALSE);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE TABLE coll_fp_subdtls_01(</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">)INHERITS (coll_fp_submission_details)</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">WITH (OIDS=FALSE)</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">TABLESPACE fpsdts01;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE TABLE coll_fp_subdtls_02(</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">)INHERITS (coll_fp_submission_details)</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">WITH (OIDS=FALSE)</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">TABLESPACE fpsdts02;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><b><font face="Courier New" size="2"><span
style="font-size:11.0pt;font-weight:bold">Inthe trigger </span></font></b><p class="MsoPlainText"><font face="Courier
New"size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE OR REPLACE FUNCTION ins_submission_details()</span></font><p class="MsoPlainText"><font face="Courier
New"size="2"><span style="font-size: 
10.0pt">        RETURNS TRIGGER AS $$</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">          DECLARE</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">          dateTable TEXT;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">          cmd TEXT;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">        BEGIN</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">      IF ((NEW.rrid % 2)= 0) THEN</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            dateTable := 'coll_fp_subdtls_01';</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">      ELSE</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">            dateTable := 'coll_fp_subdtls_02';</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">      END IF;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">      cmd := 'INSERT INTO ' || dateTable  || '(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)'
||</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt">                ' VALUES (' ||  quote_literal(NEW.rrid) || ',' ||</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.sid) || ',' ||</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.pfid) || ',' ||</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.timestamp) || ',' ||</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.schema_version) || ',' ||</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.details) || ',' ||</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">                            quote_literal(NEW.app_txn_id) || ')';</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">      EXECUTE cmd;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">      </span></font><b><font color="red" size="3"><span
style="font-size:12.0pt;color:red;font-weight:bold">RETURNNEW</span></font></b><font color="red" size="3"><span
style="font-size:12.0pt;color:red">;</span></font><fontcolor="red"><span style="color:red"></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">        END;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">      $$LANGUAGE 'plpgsql';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">If I changed the </span></font><b><font color="red" size="3"><span
style="font-size:12.0pt;color:red;font-weight:bold">RETURNNEW</span></font></b><font color="red" size="3"><span
style="font-size:12.0pt;color:red"></span></font><font size="3"><span style="font-size:12.0pt">to<font
color="red"><spanstyle="color: 
red"> <b><span style="font-weight:bold">RETURN NULL </span></b></span></font></span></font>its inserting only one
row.<pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt">But to work with hibernate I need the return NEW statement.</span></font><p class="MsoPlainText"><font
color="red"face="Courier New" size="2"><span style="font-size:10.0pt;color:red"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Please help me in resolving this.</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Thanks in advance,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">Sridhar ratna</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">-----Original Message-----<br /> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
OnBehalf Of Richard Huxton<br /> Sent: Wednesday, September 09, 2009 3:35 PM<br /> To: Sridhar Reddy Ratna<br /> Cc:
pgsql-sql@postgresql.org<br/> Subject: Re: [SQL] trigger failed to identify the partions</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Sridhar Reddy Ratna wrote:</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">>                         dateTable := coll_fp_subdtls_01;</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">>             ELSE</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">>                         dateTable := coll_fp_subdtls_02;</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">> ERROR:  column "coll_fp_subdtls_01" does not exist</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">> ERROR: column "coll_fp_subdtls_01" does not exist</span></font><p class="MsoPlainText"><font face="Courier
New"size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">I think you missed the word "column" in the error message (easy to do,</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">you know you are naming tables). You've missed the quotes around the</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">partition-names so it's trying to find a column on a table that matches.</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  dateTable := 'coll_fp_subdtls_01';</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">-- </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  Richard Huxton</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  Archonet Ltd</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">-- </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">To make changes to your subscription:</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">http://www.postgresql.org/mailpref/pgsql-sql</span></font></div><div style="border-top: solid 1px
black;padding:10px 0; margin: 20px 0; font-size: 9pt;font-family: Verdana, Arial, Helvetica, sans-serif;">DISCLAIMER<br
/>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
theinformation contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received
thiscommunication in error, please notify us by reply e-mail or directly to <a
href="mailto:netsupport@cmcltd.com">netsupport@cmcltd.com</a>or telephone and immediately and permanently delete the
messageand any attachments. Thank you.</div><div style="border-top: solid 1px black; border-bottom: solid 1px
black;padding:10px 0; margin: 20px 0; font-size: 9pt;font-family: Verdana, Arial, Helvetica, sans-serif;">This email
hasbeen scrubbed for your protection by SecureMX. For more information visit <a
href="http://securemx.in/">securemx.in</a></div>