Обсуждение: Error on dynamic code.

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

Error on dynamic code.

От
"Mark J Camilleri"
Дата:
<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>

Re: Error on dynamic code.

От
John DeSoi
Дата:
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