PostgreSQL: modifying procedure parameter

I have database table where game players can rate each other and also leave an optional comment (if they have a good enough "reputation" themselves):

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
);

A player "reputation" is the sum of all fair and nice values.

I'm trying to modify my PL/pgSQL procedure for creating such ratings, so that about comments can only be created by users with "reputation" >= 30 and the good, fair and nice values can only be set by users with "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;

Unfortunately I get the error:

ERROR:  "$7" is declared CONSTANT
CONTEXT:  compilation of PL/pgSQL function "pref_update_rep" near line 21

Which means the _about := null; assignment above fails.

Is there a nice way to make it work or do I have to introduce a temp. variable here?

Using PostgreSQL 8.4.7 and CentOS Linux 5.5.

Thank you! Alex

Function arguments in 8.4 are implicitly CONSTANT unless they're OUT parameters. I can't find where this is specified in the 8.4 documentation but I did find some relevant discussion about going from Informix to PostgreSQL:

• http://archives.postgresql.org/pgsql-interfaces/2007-08/msg00020.php
• http://archives.postgresql.org/pgsql-interfaces/2007-08/msg00023.php

Looks like you can simulate a mutable function argument simply by declaring a local variable with the same name:

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;
        _author varchar := _author;
        begin

A bit kludgy perhaps and possibly confusing when you look at it in the future but maybe that is preferable than the alternatives.

It runs in PostgreSQL 9.0.2. Maybe an upgrade is in order.