Thread: TG_TABLE_NAME as identifier

TG_TABLE_NAME as identifier

From
Tiziano Slack
Date:
<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> 

Re: TG_TABLE_NAME as identifier

From
Richard Huxton
Date:
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


Re: TG_TABLE_NAME as identifier

From
"Robins Tharakan"
Date:
Hi,

I am not sure if this'd help :

1. Are you sure that the sequence and the tablename have the same name ? [The insert statement is seeing the insert target identifier as a variable]

2. In case you need to run the  [INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.* ] statement you could always use EXECUTE to run concatenated strings.

3. Unrelated, but as an advice, I always recommend giving field names while inserting and intentionally try and and avoid insert statements such as INSERT INTO xxx SELECT * .

Robins


---------- Forwarded message ----------
From: Tiziano Slack <slack83@hotmail.it>
Date: Feb 5, 2008 2:15 PM
Subject: [SQL] TG_TABLE_NAME as identifier
To: pgsql-sql@postgresql.org


Hello everybody! I'm newbie to plpgsql syntax. Can anyone tell where I'm getting wrong?

CREATE OR REPLACE FUNCTION tr_audit() RETURNS TRIGGER AS $tr_audit$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
        ...
        NEW.id = nextval(TG_TABLE_NAME || '_id_seq'::regclass);
        INSERT INTO TG_TABLE_NAME SELECT NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
        ...
        END IF;

        RETURN NULL;
    END;
$tr_audit$ LANGUAGE 'plpgsql';

returns

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO  $1  SELECT  $2 .*
                     ^
QUERY:  INSERT INTO  $1  SELECT  $2 .*
CONTEXT:  SQL statement in PL/PgSQL function "tr_audit" near line 8

I tried some variants using INSERT INTO '|| TG_TABLE_NAME ||' SELECT NEW.* and quote_ident(TG_TABLE_NAME) with no luck! Unfortunately searches on the Net and old threads on this forum didn't help me.

Hope someone can do this!

Thanks in advance,

Tiziano.


Windows Live Spaces Push the Button! Crea il tuo blog e condividi le tue esperienze col mondo!

Re: TG_TABLE_NAME as identifier

From
Tiziano Slack
Date:
<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>

Re: TG_TABLE_NAME as identifier

From
Richard Huxton
Date:
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


Re: TG_TABLE_NAME as identifier

From
Tiziano Slack
Date:
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>