2010/11/20 Alexander Farber <alexander.farber@xxxxxxxxx>
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).
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).
Â
manually (if you need).
Â
Thank you!
Alex
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
