Hey Alexander, 2010/11/20 Alexander Farber <alexander.far...@gmail.com>
> 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...) > 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). > > Thank you! > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.