I'm actually hoping to use inet (or cidr?) instead of strings...
On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Hey Alexander,
>
> 2010/11/20 Alexander Farber <alexander.farber@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.
>
--
Sent from my mobile device
