pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedI 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>
2003-05-15 08:45 ` Re: I need help with a rule to relocate duplicate records Luis Sousa <llsousa@ualg.pt>
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-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>
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