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

Поиск
Список
Период
Сортировка

trigger failed to identify the partions

От
"Sridhar Reddy Ratna"
Дата:
<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>

Re: trigger failed to identify the partions

От
Richard Huxton
Дата:
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

Re: trigger failed to identify the partions

От
"Sridhar Reddy Ratna"
Дата:
<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>

Re: trigger failed to identify the partions

От
Richard Huxton
Дата:
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