Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8860B2E36D7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  5 Feb 2008 04:45:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 16727-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  5 Feb 2008 04:45:17 -0400 (AST)
Received: from bay0-omc2-s33.bay0.hotmail.com (bay0-omc2-s33.bay0.hotmail.com
	[65.54.246.169])
	by postgresql.org (Postfix) with ESMTP id 72EC62E36CC
	for <pgsql-sql@postgresql.org>; Tue,  5 Feb 2008 04:45:16 -0400 (AST)
Received: from BAY109-W28 ([64.4.19.128]) by bay0-omc2-s33.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Tue, 5 Feb 2008 00:45:15 -0800
Message-ID: <BAY109-W28A8D4A3264086A0C38DA5CE2C0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_a1c7964f-0628-4418-9ddb-79a1db563e77_"
X-Originating-IP: [87.3.224.121]
From: Tiziano Slack <slack83@hotmail.it>
To: <pgsql-sql@postgresql.org>
Subject: TG_TABLE_NAME as identifier
Date: Tue, 5 Feb 2008 09:45:14 +0100
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 05 Feb 2008 08:45:15.0120 (UTC)
	FILETIME=[6D65AF00:01C867D3]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/14
X-Sequence-Number: 30105

--_a1c7964f-0628-4418-9ddb-79a1db563e77_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

CREATE OR REPLACE FUNCTION tr_audit() RETURNS TRIGGER AS $tr_audit$
    BEGIN
        IF (TG_OP =3D 'UPDATE') THEN
        ...
        NEW.id =3D nextval(TG_TABLE_NAME || '_id_seq'::regclass);
        INSERT INTO TG_TABLE_NAME SELECT NEW.*;
        ELSIF (TG_OP =3D '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=0A=
tried some variants using INSERT INTO '|| TG_TABLE_NAME ||' SELECT=0A=
NEW.* and quote_ident(TG_TABLE_NAME) with no luck! Unfortunately=0A=
searches on the Net and old threads on this forum didn't help me.

Hope someone can do this!

Thanks in advance,

Tiziano.
_________________________________________________________________
Organizza le tue feste e condividine le foto con Windows Live Eventi!
http://home.services.spaces.live.com/events/=

--_a1c7964f-0628-4418-9ddb-79a1db563e77_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class=3D'hmmessage'><div style=3D"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>=
&nbsp;&nbsp;&nbsp; BEGIN<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; IF (TG_OP=
 =3D 'UPDATE') THEN<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ...<br>&nbsp;&=
nbsp;&nbsp; &nbsp;&nbsp;&nbsp; NEW.id =3D nextval(TG_TABLE_NAME || '_id_seq=
'::regclass);<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; INSERT INTO TG_TABLE=
_NAME SELECT NEW.*;<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ELSIF (TG_OP =
=3D 'INSERT') THEN<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ...<br>&nbsp;&n=
bsp;&nbsp; &nbsp;&nbsp;&nbsp; END IF;<br><br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp=
;&nbsp; RETURN NULL;<br>&nbsp;&nbsp;&nbsp; END;<br>$tr_audit$ LANGUAGE 'plp=
gsql';<br><br>returns<br><br>ERROR:&nbsp; syntax error at or near "$1"<br>L=
INE 1: INSERT INTO&nbsp; $1&nbsp; SELECT&nbsp; $2 .*<br>&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp; ^<br>QUERY:&nbsp; INSERT INTO&nbsp; $1&nbsp; SEL=
ECT&nbsp; $2 .*<br>CONTEXT:&nbsp; SQL statement in PL/PgSQL function "tr_au=
dit" near line 8<br><br>I=0A=
tried some variants using INSERT INTO '|| TG_TABLE_NAME ||' SELECT=0A=
NEW.* and quote_ident(TG_TABLE_NAME) with no luck! Unfortunately=0A=
searches on the Net and old threads on this forum didn't help me.<br><br>Ho=
pe someone can do this!<br><br>Thanks in advance,<br><br>Tiziano.</div><br =
/><hr />Windows Live Spaces <a href=3D'http://pushthebutton2006.spaces.live=
.com/' target=3D'_new'>Push the Button! Crea il tuo blog e condividi le tue=
 esperienze col mondo!</a></body>
</html>=

--_a1c7964f-0628-4418-9ddb-79a1db563e77_--