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

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