Search Postgresql Archives

Re: Comparing first 3 numbers of a IPv4 address?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm actually hoping to use inet (or cidr?) instead of strings...



On 11/20/10, Dmitriy Igrishin <dmitigr@xxxxxxxxx> wrote:
> Hey Alexander,
>
> 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).
>
>
>> 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@xxxxxxxxxxxxxx)
>> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux