Re: Comparing first 3 numbers of a IPv4 address? - Mailing list pgsql-general

I think inet is a number internally, there is probably a more effective way...

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> You can. host() just extract IP address from a value of inet
> type as text, string_to_array() converts this text to text[], and
> it makes it possible to compare with another text[]... I see no
> problem here. It works just fine.
> But probably there is another (better) solution...
>
> 2010/11/20 Alexander Farber <alexander.farber@gmail.com>
>
>> 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
>>
>> --
>> 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