But you want to compare IP addresses by 3 first parts. I don't see any
function or operator at SQL level which allow to do it without converting
to text.
Probably, you can do it by manipulating a binary form (from libpq).
But is it worth it ? :-)
2010/11/20 Alexander Farber <alexander.farber@xxxxxxxxx>
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
--
// Dmitriy.
