Thread: trigger failed to identify the partions
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have created a table and partitions as below.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE coll_fp_submission_details</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> rrid numeric NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> sid numeric NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> pfid numeric NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> "timestamp" date NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> schema_version numeric NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> details character varying NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> app_txn_id character varying NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">WITH (OIDS=FALSE);</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE coll_fp_subdtls_01</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">INHERITS (coll_fp_submission_details)</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">WITH (OIDS=FALSE)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">TABLESPACE fpsdts01;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE coll_fp_subdtls_02</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">INHERITS (coll_fp_submission_details)</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">WITH (OIDS=FALSE)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">TABLESPACE fpsdts02;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial;font-weight:bold">Now created a trigger as below</span></font></b><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE OR REPLACE FUNCTION ins_submission_details()</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> RETURNS TRIGGER AS $$</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> DECLARE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> dateTable TEXT;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> cmd TEXT;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> BEGIN</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> IF ((NEW.rrid % 2)= 0) THEN</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> dateTable := coll_fp_subdtls_01;</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> ELSE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> dateTable := coll_fp_subdtls_02;</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> END IF;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> cmd := 'INSERT INTO ' || dateTable ||
'(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)'||</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> ' VALUES (' || quote_ident(NEW.rrid) || ',' ||</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_ident(NEW.sid) || ',' ||</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_ident(NEW.pfid) || ',' ||</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_literal(NEW.timestamp) || ',' ||</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_ident(NEW.schema_version) || ',' ||</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_literal(NEW.details) || ',' ||</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> quote_literal(NEW.app_txn_id) || ',';</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> EXECUTE cmd;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> RETURN NULL;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> END;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> $$LANGUAGE 'plpgsql';</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TRIGGER trig_ins_submission_details</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> BEFORE INSERT</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> ON coll_fp_submission_details</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> FOR EACH ROW</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> EXECUTE PROCEDURE ins_submission_details();</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span style="font-size:11.0pt;
font-family:Arial;font-weight:bold">Now I am trying to insert data into table </span></font></b><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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');</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The error is </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">ERROR: column "coll_fp_subdtls_01" does not exist</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">LINE 1: SELECT coll_fp_subdtls_01</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> ^</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">QUERY: SELECT coll_fp_subdtls_01</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">CONTEXT: PL/pgSQL function "ins_submission_details" line 7 at assignment</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">********** Error **********</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">ERROR: column "coll_fp_subdtls_01" does not exist</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">SQL state: 42703</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Context: PL/pgSQL function "ins_submission_details" line 7 at assignment</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Can any body help me what is this problem and what is the solution.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks in advance,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Sridhar Ratna</span></font></div><div style="border-top: solid 1px black;padding: 10px 0; margin:
20px0; font-size: 9pt;font-family: Verdana, Arial, Helvetica, sans-serif;">DISCLAIMER<br /> The information contained
inthis e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the
intendedrecipient, any dissemination, use, review, distribution, printing or copying of the information contained in
thise-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error,
pleasenotify us by reply e-mail or directly to <a href="mailto:netsupport@cmcltd.com">netsupport@cmcltd.com</a> or
telephoneand immediately and permanently delete the message and 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 has been scrubbed for your protection by SecureMX.
Formore information visit <a href="http://securemx.in/">securemx.in</a></div>
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
<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>
Sridhar Reddy Ratna wrote: > > cmd := 'INSERT INTO ' || dateTable || > EXECUTE cmd; > RETURN NEW; > If I changed the RETURN NEW to RETURN NULL its inserting only one row. Yes. RETURN NEW allows the insert to procede normally so you end up with the two rows. > But to work with hibernate I need the return NEW statement. Hmm - not sure I can see a way around this. Maybe someone else who knows Hibernate can help. -- Richard Huxton Archonet Ltd