TG_TABLE_NAME as identifier

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

TG_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>
  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 09:32  Richard Huxton <dev@archonet.com>
  parent: Tiziano Slack <slack83@hotmail.it>
  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 10:07  Robins Tharakan <tharakan@gmail.com>
  parent: Tiziano Slack <slack83@hotmail.it>
  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>
  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:52  Richard Huxton <dev@archonet.com>
  parent: Tiziano Slack <slack83@hotmail.it>
  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