From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Comparing first 3 numbers of a IPv4 address? |
Date: | 2010-11-20 13:31:09 |
Message-ID: | AANLkTik-w5syJoXZRd6Y7LyJvpQAvHKBQ6iOBqTVzbhy@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:
create table pref_rep (
id varchar(32) references pref_users(id) check (id <> author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);
To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:
create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin
delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';
update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;
if not found then
insert into pref_rep(id, author, author_ip, good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;
I have 2 questions please:
1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)
2) Do I need to add an index to my table
or are id and author indexed already?
Thank you!
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2010-11-20 13:32:46 | Re: [pgsql-www] Forums at postgresql.com.au |
Previous Message | Michael Glaesemann | 2010-11-20 13:28:20 | Re: [pgsql-www] Forums at postgresql.com.au |