Thread: I need help with a rule to relocate duplicate records

I made a rule :

CREATERULE ins_2003_may
ASON INSERT
TOdetail_2003may
WHEREEXISTS ( 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" )
 
DOINSTEAD 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

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