PostgreSQL: modifying procedure parameter

Question

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

I'm not that good at SQL, what do you mean? The id references another table - the pref_users, and there the id is primary key — Alexander Farber
– Alexander Farber, Commented Feb 19, 2011 at 17:21
I think Catcall is noting that pref_rep doesn't have a primary key or, perhaps, an index on id. Indexing the referencing column is a good idea as the DB has to check the FKs when the referenced table changes. — mu is too short
– mu is too short, Commented Feb 20, 2011 at 0:22

mu is too short · Accepted Answer · 2011-02-19 18:40:52Z

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:

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.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-02-19 16:48:19Z

1

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

answered Feb 19, 2011 at 16:48

Mike Sherrill 'Cat Recall'

96.6k20 gold badges132 silver badges195 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: modifying procedure parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related