Обсуждение: Error on dynamic code.
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I am trying to write a dynamic trigger function on insert operations so that the same function can beused across all my tables. In this case my tables are ‘test’ and ‘test_a’ (my convention is that all audit table namesare the name of the <i><span style="font-style:italic">original</span></i> table concatenated with ‘_a’). Below ispart of my code in plpgsql:</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">CREATE OR REPLACE FUNCTION audit_insert()</span></font><p class="MsoNormal"><font face="CourierNew" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> RETURNS "trigger" AS</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">$BODY$</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">DECLARE</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> new_audit_row RECORD;</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New""> dynamic_SQL text;</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">BEGIN</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> --Instantiate new_audit_row to the required type.</span></font><p class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> dynamic_SQL := 'SELECT INTO new_audit_row * ' ||</span></font><p class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> 'FROM ' || quote_ident(TG_RELNAME || '_a') || ';';</span></font><p class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> EXECUTE dynamic_SQL;</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> --... more code here</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">END;</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">$BODY$</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> LANGUAGE 'plpgsql' VOLATILE;</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">However, when I try to insert data in ‘test’ I am getting the following error message:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";font-weight:bold">ERROR: syntax error at or near "INTO" at character 8</span></font></b><pclass="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";font-weight:bold">QUERY: SELECT INTO new_audit_row * FROM "test_a";</span></font></b><pclass="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";font-weight:bold">CONTEXT: PL/pgSQL function "audit_insert" line 18 at execute statement</span></font></b><pclass="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">The funny thing is that the documentation I read about SELECT INTO and RECORD types give the followingexample, amongst others:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><pre><font face="Courier New" size="2"><span style="font-size:10.0pt">DECLARE</span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt"> users_rec RECORD;</span></font></pre><pre><font face="Courier New" size="2"><span style="font-size:10.0pt">BEGIN</span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt"> SELECT INTO users_rec * FROM users WHERE user_id=3;</span></font></pre><pre><font face="CourierNew" size="2"><span style="font-size:10.0pt"> </span></font></pre><pre><font face="Courier New" size="2"><spanstyle="font-size:10.0pt">--...more code</span></font></pre><pre><font face="Courier New" size="2"><span style="font-size:10.0pt">END;</span></font></pre><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><i><font face="Arial" size="1"><span style="font-size:8.0pt; font-family:Arial;font-style:italic">(full code can be found at <a href="http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT">http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT</a> )</span></font></i><pclass="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">Which is basically identical (except for the WHERE clause) to the query returned in the my error message!!</span></font><pclass="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 anyone help please?</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">Regs,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Mark J Camilleri</span></font></div>
On Jul 14, 2005, at 3:46 AM, Mark J Camilleri wrote: > > > The funny thing is that the documentation I read about SELECT INTO > and RECORD types give the following example, amongst others: See the section below that on EXECUTE: > The results from SELECT commands are discarded by EXECUTE, and > SELECT INTO is not currently supported within EXECUTE. So there is > no way to extract a result from a dynamically-created SELECT using > the plain EXECUTE command. There are two other ways to do it, > however: one is to use the FOR-IN-EXECUTE loop form described in > Section 35.7.4, and the other is to use a cursor with OPEN-FOR- > EXECUTE, as described in Section 35.8.2. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL