Re: Comparing first 3 numbers of a IPv4 address?

Hey Alexander,

2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.