Thread: TG_TABLE_NAME as identifier

<div style="text-align: left;">Hello everybody! I'm newbie to plpgsql syntax. Can anyone tell where I'm getting
wrong?<br/><br />CREATE OR REPLACE FUNCTION tr_audit() RETURNS TRIGGER AS $tr_audit$<br />    BEGIN<br />        IF
(TG_OP= 'UPDATE') THEN<br />        ...<br />        NEW.id = nextval(TG_TABLE_NAME || '_id_seq'::regclass);<br />   
   INSERT INTO TG_TABLE_NAME SELECT NEW.*;<br />        ELSIF (TG_OP = 'INSERT') THEN<br />        ...<br />        END
IF;<br/><br />        RETURN NULL;<br />    END;<br />$tr_audit$ LANGUAGE 'plpgsql';<br /><br />returns<br /><br
/>ERROR: syntax error at or near "$1"<br />LINE 1: INSERT INTO  $1  SELECT  $2 .*<br />                     ^<br
/>QUERY: INSERT INTO  $1  SELECT  $2 .*<br />CONTEXT:  SQL statement in PL/PgSQL function "tr_audit" near line 8<br
/><br/>I tried some variants using INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.* and quote_ident(TG_TABLE_NAME) with
noluck! Unfortunately searches on the Net and old threads on this forum didn't help me.<br /><br />Hope someone can do
this!<br/><br />Thanks in advance,<br /><br />Tiziano.</div><br /><hr />Windows Live Spaces <a
href="http://pushthebutton2006.spaces.live.com/"target="_new">Push the Button! Crea il tuo blog e condividi le tue
esperienzecol mondo!</a> 

Tiziano Slack wrote:
> Hello everybody! I'm newbie to plpgsql syntax. Can anyone tell where
> I'm getting wrong?

> INSERT INTO TG_TABLE_NAME SELECT NEW.*;

If you need to build a dynamic query with plpgsql you'll need to 
assemble it as a string and use EXECUTE. You can use variables in 
comparisons etc. but not as column-names or table-names.

You might want to look at pltcl or plperl etc. if you are going to be 
doing a lot of this sort of stuff.

--   Richard Huxton  Archonet Ltd

<div style="text-align: left;"><div style="text-align: left;">Thanks for the precious tips, but unfortunately the
syntax:<br/><pre class="EC_data1"><span class="EC_keyword">EXECUTE</span> <span class="EC_literal">'INSERT INTO
'</span>||TG_TABLE_NAME ||<span class="EC_literal">' SELECT </span><span class="EC_keyword">'||
NEW</span>.*;</pre></div>isparsed correctly, but when the trigger is fired the following error is returned<br /><br
/>ERROR: column "*" not found in data type mytable<br />CONTEXT:  SQL statement "SELECT  'INSERT INTO '||  $1  ||'
SELECT'||  $2 .*"<br /><br />Using <br /><pre class="EC_data1"><span class="EC_keyword">EXECUTE</span> <span
class="EC_literal">'INSERTINTO '</span>|| TG_TABLE_NAME ||<span class="EC_literal">' SELECT NEW.*'</span>;<br
/></pre>instead,returns:<br /><br />ERROR:  NEW used in query that is not in a rule<br />CONTEXT:  SQL statement
"INSERTINTO anag_referenti SELECT NEW.*"<br /><br />Hope someone can show me another way to do that, if exists ;)<br
/><br/>Have a nice day!<br /><br />T.</div><br /><hr />Raccolta foto di Windows Live <a
href="http://get.live.com/photogallery/overview"target="_new">Ordina e condividi le tue foto in maniera semplice e
veloce!</a>

Tiziano Slack wrote:
> Thanks for the precious tips, but unfortunately the syntax:
> EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT '|| NEW.*;is parsed correctly, but when the trigger is fired the
followingerror is returned
 
> 
> ERROR:  column "*" not found in data type mytable
> CONTEXT:  SQL statement "SELECT  'INSERT INTO '||  $1  ||' SELECT '||  $2 .*"
> 
> Using 
> EXECUTE 'INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.*';
> instead, returns:
> 
> ERROR:  NEW used in query that is not in a rule
> CONTEXT:  SQL statement "INSERT INTO anag_referenti SELECT NEW.*"
> 
> Hope someone can show me another way to do that, if exists ;)

You'll have to build the INSERT in full - column by column.

Have you considered plperl / pltcl etc. - that makes it easier to do.

--   Richard Huxton  Archonet Ltd

Thanks,<br /><br />T.<br /><br /><hr />Windows Live Spaces <a href="http://pushthebutton2006.spaces.live.com/"
target="_new">Pushthe Button! Crea il tuo blog e condividi le tue esperienze col mondo!</a>