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...)
You may try something like this (this solution can be better): SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3]; to get first 3 bytes of IP in array (ready to compare with another array).
2) Do I need to add an index to my table or are id and author indexed already?
Foreign keys columns does not indexed. You should create them manually (if you need).