X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BD1C04764BB
	for <pgsql-sql@postgresql.org>; Wed, 14 May 2003 18:11:27 -0400 (EDT)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	ESMTP id 61940-06 for <pgsql-sql@postgresql.org>;
	Wed, 14 May 2003 18:11:26 -0400 (EDT)
Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1])
	by postgresql.org (Postfix) with ESMTP id B6B024764AD
	for <pgsql-sql@postgresql.org>; Wed, 14 May 2003 18:11:25 -0400 (EDT)
Received: from incentre.net (squid.incentre.net [206.75.213.195])
	by bach.ccinet.ab.ca (8.12.6/8.12.6) with ESMTP id h4EMDCYv010272
	for <pgsql-sql@postgresql.org>; Wed, 14 May 2003 16:13:12 -0600 (MDT)
	(envelope-from guy@incentre.net)
Message-ID: <3EC2BF0F.1070709@incentre.net>
Date: Wed, 14 May 2003 16:11:27 -0600
From: Guy Fraser <guy@incentre.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: I need help with a rule to relocate duplicate records
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/304
X-Sequence-Number: 13469

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