I will be glad to see the best solution, so if you find it please share. :-)
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.far...@gmail.com>
> I think inet is a number internally, there is probably a more effective
> way...
>
> On 11/20/10, Dmitriy Igrishin <dmit...@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.far...@gmail.com>
> >
> >> I'm actually hoping to use inet (or cidr?) instead of strings...
> >>
> >>
> >>
> >> On 11/20/10, Dmitriy Igrishin <dmit...@gmail.com> wrote:
> >> > Hey Alexander,
> >> >
> >> > 2010/11/20 Alexander Farber <alexander.far...@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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
// Dmitriy.
