Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 773482E3577 for ; Tue, 5 Feb 2008 06:20:43 -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 37960-01-8 for ; Tue, 5 Feb 2008 06:20:26 -0400 (AST) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.189]) by postgresql.org (Postfix) with ESMTP id B86DF2E3307 for ; Tue, 5 Feb 2008 06:07:33 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so2698989fka.3 for ; Tue, 05 Feb 2008 02:07:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=6PayClSy+5teVyJkI5+OIjM3HHjDpTdJyUCtPMJH9HE=; b=d1CTvz5idRo4paoQCcZ/mnZoiaknj1ICkYwEorxwHP6W/Mbi9kLy6VqVTs/Ctdv5tm+AXCHvdqjxu8irkWh9S7C8TXVqrmSrZsNaO/f9gB5dghtYoBbEMB2whYW4sP4WeXB89Y/fnFMo6oBUfUQz8Pc9SBosbooDTi+AYvSL79c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=iFM1chqduwCxyIqaA8oeoynv3aoPxuQAvHaKUx/LiN3yK7GiGFPDyDisPzOlTIGJPkQzyaO6bhdQGoNpGigMaUjAy05kM0Gq5A1c4emxFPcUrYFmSPD2vw4fYxL/vGccxrOzvn+ZTLWg0wfrFOH1W3SRApejhub3R0vzb352PIw= Received: by 10.82.138.6 with SMTP id l6mr15083539bud.13.1202206051518; Tue, 05 Feb 2008 02:07:31 -0800 (PST) Received: by 10.82.113.9 with HTTP; Tue, 5 Feb 2008 02:07:31 -0800 (PST) Message-ID: <36af4bed0802050207t27948f7bh754b5b25762302d7@mail.gmail.com> Date: Tue, 5 Feb 2008 15:37:31 +0530 From: "Robins Tharakan" Reply-To: robins@pobox.com To: "Tiziano Slack" Subject: Re: TG_TABLE_NAME as identifier Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7536_20325869.1202206051516" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/16 X-Sequence-Number: 30107 ------=_Part_7536_20325869.1202206051516 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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! ------=_Part_7536_20325869.1202206051516 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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!

------=_Part_7536_20325869.1202206051516--