postgresql plpsql function with IF ELSE statement

Question

Suppose i have a table named t which is stored in a postgresql database. I have 6 columns named a, b, c, d, e, f. Columns a, b and c take values between 0 and 100, but on an aritmetic scale 0 < a < b < c < 100. Columns d, e and f take any value in the range 1 to 10.

I want to compute the weighted average of columns d, e and f, but with a condition related to columns a, b and c. The condition is that the average will be computed only on the a, b and c columns that have values smaller than 50.

I think this need a function so I started to do it:

CREATE OR REPLACE FUNCTION example(t character varying, a character varying, b character varying, c character varying, d character varying, e character varying, f character varying, g character varying) RETURNS double precision AS $$

BEGIN
    ALTER TABLE t ADD COLUMN g double precision;
        UPDATE t
            IF a > 50 THEN
                SET g = d;
            ELSE 
                IF b > 50 THEN;
                    SET g = (d+e)/2;
                END IF c > 50 THEN
                        SET g = (d+e+f)/3;
            END IF;
END;
$$ LANGUAGE plpgsql;

I get the following error:

ERROR:  syntax error at or near "$1"
LINE 1: ALTER TABLE  $1  ADD COLUMN  $2  double precision
                 ^
QUERY:  ALTER TABLE  $1  ADD COLUMN  $2  double precision
CONTEXT:  SQL statement in PL/PgSQL function "example" near line 2

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "example" near line 2

Can someone tell me were I am wrong so I can go ahead with computing the needed average?

Answer 1

The immediate cause of the error are naming conflicts. You define parameters t and g and use the same names in your ALTER TABLE statement. I make it a habit to prefix parameter names (like _t, _g) so they won't conflict with other names in the function body.

Also, your parameters should not be defined character varying since they hold numerical values. Use an appropriate numerical type, probably double precision.

But I don't think you need a function at all. This can be solved with plain SQL statements:

ALTER TABLE tbl ADD COLUMN g double precision;

UPDATE tbl
SET g = CASE
           WHEN a > 50 THEN d 
           WHEN b > 50 THEN (d+e)/2
           WHEN c > 50 THEN (d+e+f)/3
           ELSE 0  -- definition for ELSE case is missing
        END;

You could also scratch the whole idea completely and use a view for the purpose, as g only holds functionally dependent data:

CREATE VIEW tbl_with_g AS
SELECT *
     , CASE
          WHEN a > 50 THEN d 
          WHEN b > 50 THEN (d+e)/2
          WHEN c > 50 THEN (d+e+f)/3
          ELSE 0
       END AS g
FROM   tbl;