title: PostgreSQL: modifying procedure parameter
body: <p>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):</p>
  
  <pre><code>create table pref_rep (
          id varchar(32) references pref_users(id) check (id &lt;&gt; 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
  );
  </code></pre>
  
  <p>A player "reputation" is the sum of all <strong>fair</strong> and <strong>nice</strong> values.</p>
  
  <p>I'm trying to modify my PL/pgSQL procedure for creating such ratings, so that <strong>about</strong> comments can only be created by users with "reputation" >= 30 and the <strong>good</strong>, <strong>fair</strong> and <strong>nice</strong> values can only be set by users with "reputation" > 0:</p>
  
  <pre><code>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 &lt;= 0) then
                  return;
          end if;
  
          if (rep &lt; 30) then
                  _about := null;
          end if;
  
          delete from pref_rep
          where id = _id and
          age(last_rated) &lt; interval '1 hour' and
          (author_ip &amp; '255.255.255.0'::inet) =
          (_author_ip &amp; '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;
  </code></pre>
  
  <p>Unfortunately I get the error:</p>
  
  <pre><code>ERROR:  "$7" is declared CONSTANT
  CONTEXT:  compilation of PL/pgSQL function "pref_update_rep" near line 21
  </code></pre>
  
  <p>Which means the <strong>_about := null;</strong> assignment above fails.</p>
  
  <p>Is there a nice way to make it work or do I have to introduce a temp. variable here?</p>
  
  <p>Using PostgreSQL 8.4.7 and CentOS Linux 5.5.</p>
  
  <p>Thank you!
  Alex</p>