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.orgHello 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!