I think inet is a number internally, there is probably a more effective way...
On 11/20/10, Dmitriy Igrishin <dmitigr@xxxxxxxxx> 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@xxxxxxxxx>
>
>> 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
>>
>
>
>
> --
> // 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
