DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=W5tObccur8eduHre0YHE1PBJMtJ8vsrYuOtxPnqRo7Slb6Yj1N9xaopK6Yr41RhQbAfpnn17UhOnKjBQi3Lb+15EcEUrxsXVE1subq3hjD1gCNUlf7Hn8tX5aULruMb+1UvMrjmNSP4J8cw5c9oFavRCXvcXAhjA5wN1FZ5WgmU=
Message-ID: <162867790708110910v414d7cb3x6e7b4cd16fe4ff80@mail.gmail.com>
Date: Sat, 11 Aug 2007 18:10:04 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
Cc: "Javier Fonseca V." <fonsecajavier@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <2071.1186847441@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com>
	<86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com>
	<162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com>
	<2071.1186847441@sss.pgh.pa.us>

2007/8/11, Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
> > NEW is only plpgsql variable. It isn't visible on SQL level.
>
> Correct, but:
>
> > You cannot use new.*, you can:
> > execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b ....
>
> You're both overthinking the problem.  In recent releases (at least
> since 8.2) you can do it without any EXECUTE.  Like this:
>
> regression=# create table mytab (f1 int, f2 text);
> CREATE TABLE
> regression=# create table logt  (f1 int, f2 text, ts timestamptz);
> CREATE TABLE
> regression=# create function myt() returns trigger as $$
> regression$# begin
> regression$#   insert into logt values(new.*, now());
> regression$#   return new;
> regression$# end$$ language plpgsql;
> CREATE FUNCTION
>

I know it Tom. But original question contains

EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || ' SELECT new.*';

and then he needs EXECUTE (propably).

but new.* in insert is nice feature.

Regards
Pavel Stehule