pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

I need help with a rule to relocate duplicate records
2+ messages / 2 participants
[nested] [flat]

* I need help with a rule to relocate duplicate records
@ 2003-05-14 22:11  Guy Fraser <guy@incentre.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Guy Fraser @ 2003-05-14 22:11 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: I need help with a rule to relocate duplicate records
@ 2003-05-15 08:45  Luis Sousa <llsousa@ualg.pt>
  parent: Guy Fraser <guy@incentre.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Luis Sousa @ 2003-05-15 08:45 UTC (permalink / raw)
  To: Guy Fraser <guy@incentre.net>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-05-15 08:45 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-14 22:11 I need help with a rule to relocate duplicate records Guy Fraser <guy@incentre.net>
2003-05-15 08:45 ` Luis Sousa <llsousa@ualg.pt>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox