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.
