X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 757C04765F1
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 04:45:51 -0400 (EDT)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	ESMTP id 58488-01 for <pgsql-sql@postgresql.org>;
	Thu, 15 May 2003 04:45:49 -0400 (EDT)
Received: from smtp.ualg.pt (smtp.ualg.pt [193.136.224.8])
	by postgresql.org (Postfix) with ESMTP id 6582D4765FA
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 04:45:49 -0400 (EDT)
Received: from localhost (smtp.ualg.pt [127.0.0.1])
	by smtp.ualg.pt (Postfix) with ESMTP
	id 795F64BD16; Thu, 15 May 2003 09:45:50 +0100 (WEST)
Received: from smtp.ualg.pt ([127.0.0.1])
	by localhost (smtp.ualg.pt [127.0.0.1]) (amavisd-new,
	port 10024) with SMTP
	id 28350-02; Thu, 15 May 2003 09:45:48 +0100 (WEST)
Received: from ualg.pt (corisco.uceh.ualg.pt [10.10.20.126])
	by smtp.ualg.pt (Postfix) with ESMTP
	id 03F454BDB3; Thu, 15 May 2003 09:45:48 +0100 (WEST)
Message-ID: <3EC353BF.7040206@ualg.pt>
Date: Thu, 15 May 2003 09:45:51 +0100
From: Luis Sousa <llsousa@ualg.pt>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.2.1) Gecko/20021226 Debian/1.2.1-9
X-Accept-Language: en-pt
MIME-Version: 1.0
To: Guy Fraser <guy@incentre.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: I need help with a rule to relocate duplicate records
References: <3EC2BF0F.1070709@incentre.net>
In-Reply-To: <3EC2BF0F.1070709@incentre.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/310
X-Sequence-Number: 13475

Guy Fraser wrote:

> I made a rule :
>
> CREATE
> RULE
> ins_2003_may
> AS
> ON
> INSERT
> TO
> detail_2003may
> WHERE
> EXISTS
> (
> SELECT
> "Acct-Status-Type",
> "User-Name",
> "Realm",
> "Called-Station-Id",
> "Calling-Station-Id",
> "Service-Type",
> "Framed-Protocol",
> "Client-IP-Address",
> "NAS-IP-Address",
> "NAS-Port-Type",
> "NAS-Port-Id",
> "Acct-Session-Id"
> FROM
> detail_2003may
> WHERE
> "Acct-Status-Type" = NEW."Acct-Status-Type" AND
> "User-Name" = NEW."User-Name" AND
> "Realm" = NEW."Realm" AND
> "Called-Station-Id" = NEW."Called-Station-Id" AND
> "Calling-Station-Id" = NEW."Calling-Station-Id" AND
> "Service-Type" = NEW."Service-Type" AND
> "Framed-Protocol" = NEW."Framed-Protocol" AND
> "Client-IP-Address" = NEW."Client-IP-Address" AND
> "NAS-IP-Address" = NEW."NAS-IP-Address" AND
> "NAS-Port-Type" = NEW."NAS-Port-Type" AND
> "NAS-Port-Id" = NEW."NAS-Port-Id" AND
> "Acct-Session-Id" = NEW."Acct-Session-Id"
> )
> DO
> INSTEAD
> INSERT
> INTO
> dups_2003may
> VALUES
> (
> NEW."Time-Stamp",
> NEW."Acct-Status-Type",
> NEW."User-Name",
> NEW."User-Realm",
> NEW."Realm",
> NEW."Acct-Session-Time",
> NEW."Acct-Input-Octets",
> NEW."Acct-Output-Octets",
> NEW."Called-Station-Id",
> NEW."Calling-Station-Id",
> NEW."Acct-Terminate-Cause",
> NEW."Framed-IP-Address",
> NEW."Service-Type",
> NEW."Framed-Protocol",
> NEW."Client-IP-Address",
> NEW."NAS-IP-Address",
> NEW."NAS-Port-Type",
> NEW."NAS-Port-Id",
> NEW."Timestamp",
> NEW."Acct-Session-Id",
> NEW."Acct-Link-Count",
> NEW."Acct-Multi-Session-Id",
> NEW."Acct-Delay-Time"
> )
> ;
>
> What I wanted was that when data is inserted into detail_2003may then :
>
> If a similar record exists then only insert the data into dups_2003may,
> otherwise only insert the "uniq" data into detail_2003may.
>
> What is happening is that all data is being put into dups_2003may, and
> only records with "uniq" data is being put into detail_2003may.
>
> Is there a way to do what I wanted?
>
> Guy
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
Actually I didn't read all you mail, but usually in this cases I use 
functions in plpgsql.

Luis Sousa