pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTG_TABLE_NAME as identifier
6+ messages / 3 participants
[nested] [flat]
* TG_TABLE_NAME as identifier
@ 2008-02-05 08:45 Tiziano Slack <slack83@hotmail.it>
2008-02-05 09:32 ` Re: TG_TABLE_NAME as identifier Richard Huxton <dev@archonet.com>
2008-02-05 10:07 ` Re: TG_TABLE_NAME as identifier Robins Tharakan <tharakan@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tiziano Slack @ 2008-02-05 08:45 UTC (permalink / raw)
To: pgsql-sql
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.
_________________________________________________________________
Organizza le tue feste e condividine le foto con Windows Live Eventi!
http://home.services.spaces.live.com/events/=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TG_TABLE_NAME as identifier
2008-02-05 08:45 TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
@ 2008-02-05 09:32 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2008-02-05 09:32 UTC (permalink / raw)
To: Tiziano Slack <slack83@hotmail.it>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TG_TABLE_NAME as identifier
2008-02-05 08:45 TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
@ 2008-02-05 10:07 ` Robins Tharakan <tharakan@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Robins Tharakan @ 2008-02-05 10:07 UTC (permalink / raw)
To: Tiziano Slack <slack83@hotmail.it>; +Cc: pgsql-sql
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! <http://pushthebutton2006.spaces.live.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TG_TABLE_NAME as identifier
@ 2008-02-07 07:16 Tiziano Slack <slack83@hotmail.it>
2008-02-07 07:52 ` Re: TG_TABLE_NAME as identifier Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tiziano Slack @ 2008-02-07 07:16 UTC (permalink / raw)
To: pgsql-sql
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 following error 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 ;)
Have a nice day!
T.
_________________________________________________________________
Ti piace giocare con le lettere? Prova ABCLive!
http://messengergiochi.it.msn.com/=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TG_TABLE_NAME as identifier
2008-02-07 07:16 Re: TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
@ 2008-02-07 07:52 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2008-02-07 07:52 UTC (permalink / raw)
To: Tiziano Slack <slack83@hotmail.it>; +Cc: pgsql-sql
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 following error 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TG_TABLE_NAME as identifier
@ 2008-02-07 23:10 Tiziano Slack <slack83@hotmail.it>
0 siblings, 0 replies; 6+ messages in thread
From: Tiziano Slack @ 2008-02-07 23:10 UTC (permalink / raw)
To: pgsql-sql
Thanks,
T.
_________________________________________________________________
Organizza le tue feste e condividine le foto con Windows Live Eventi!
http://home.services.spaces.live.com/events/=
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-02-07 23:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-05 08:45 TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
2008-02-05 09:32 ` Richard Huxton <dev@archonet.com>
2008-02-05 10:07 ` Robins Tharakan <tharakan@gmail.com>
2008-02-07 07:16 Re: TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
2008-02-07 07:52 ` Richard Huxton <dev@archonet.com>
2008-02-07 23:10 Re: TG_TABLE_NAME as identifier Tiziano Slack <slack83@hotmail.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox