I have a database table in which game players can rate each other, as well as leave an optional comment (if they have a good enough reputation):
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,
fair boolean,
nice boolean,
about varchar(256),
last_rated timestamp default current_timestamp
);
The player "reputation" is the sum of all fair and good values.
I am trying to modify my PL / pgSQL procedure to create ratings so that comments about can only be created by users with a "reputation"> = 30 and good , fair and nice can only be set by users with a "reputation"> 0:
create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean, _fair boolean, _nice boolean,
_about varchar) returns void as $BODY$
declare
rep integer;
begin
select
count(nullif(fair, false)) +
count(nullif(nice, false)) -
count(nullif(fair, true)) -
count(nullif(nice, true))
into rep from pref_rep where id=_author;
if (rep <= 0) then
return;
end if;
if (rep < 30) then
_about := null;
end if;
delete from pref_rep
where id = _id and
age(last_rated) < interval '1 hour' and
(author_ip & '255.255.255.0'::inet) =
(_author_ip & '255.255.255.0'::inet);
update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
fair = _fair,
nice = _nice,
about = _about,
last_rated = current_timestamp
where id = _id and author = _author;
if not found then
insert into pref_rep(id, author, author_ip, good, fair, nice, about)
values (_id, _author, _author_ip, _good, _fair, _nice, _about);
end if;
end;
$BODY$ language plpgsql;
Sorry, I get an error message:
ERROR: "$7" is declared CONSTANT
CONTEXT: compilation of PL/pgSQL function "pref_update_rep" near line 21
, _about: = null; .
, . ?
PostgreSQL 8.4.7 CentOS Linux 5.5.
!
Alex