I need help with a rule to relocate duplicate records

From: Guy Fraser <guy(at)incentre(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: I need help with a rule to relocate duplicate records
Date: 2003-05-14 22:11:27
Message-ID: 3EC2BF0F.1070709@incentre.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steven Chau 2003-05-15 02:04:54 Newbie question: How to CREATE FUNCTION
Previous Message Larry Rosenman 2003-05-14 21:41:18 Re: PL/pgSQL: Logging Trigger. Advice/comments/other?