CODEWITHSUNDEEP
postgresqlplpgsqlinetaddress
Alexander Farber
Alexander Farber

Reputation: 23038

PostgreSQL: Comparing first 3 numbers of an IPv4 address?

I'm trying to program a PHP-script, where users can rate the "goodness" of the other users:

   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 during the course of last hour

(occasional false positives because of proxies/routers are ok - because it's ok to lose a rating, since the author can re-submit it at any later time; but it's not ok to have some idiot registered under different id's and spoiling my whole database while I'm away from the web site):

   /* _author_ip will be $_SERVER['REMOTE_ADDR'] */
   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 types of IPv4 networks, doesn't matter here...)

2) Do I need to add an index to my table or are id and author indexed already?

Thank you! Alex

Upvotes: 1

Views: 263

Answers (1)

aib
aib

Reputation: 47001

1) You can use the & operator:

postgres=# SELECT '1.2.3.4'::inet & '255.255.255.0'::inet;
 ?column?
----------
 1.2.3.0
(1 row)

So, you can compare

(author_ip & '255.255.255.0'::inet) = (_author_ip & '255.255.255.0'::inet)

2) I don't know, are they?

Upvotes: 3

Related Questions