Geek asks and answers

Postgresql plpsql function with IF ELSE statement

Suppose I have a table named t that is stored in a postgresql database. I have 6 columns with names a, b, c, d, e, f. Columns a, b and c take values ​​from 0 to 100, but on an arithmetic scale 0 <a <b <c <100. Columns d, e and f take any value in the range from 1 to 10.

I want to calculate the weighted average of columns d, e, and f, but with the condition associated with columns a, b, and c. The condition is that the average value will be calculated only on columns a, b and c, which have values ​​less than 50.

I think a function is needed for this, so I started doing 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 that I'm wrong, so I can continue to calculate the required average?

+3
2

, . " ", :

CREATE FUNCTION g(rec t)
  RETURNS double precision
  IMMUTABLE
  LANGUAGE SQL
AS $$
  SELECT CASE
           WHEN $1.a > 50 THEN $1.d 
           WHEN $1.b > 50 THEN ($1.d+$1.e)/2
           WHEN $1.c > 50 THEN ($1.d+$1.e+$1.f)/3
           ELSE 0
         END;
$$;

g , , . :

SELECT *, t.g FROM t;

, PostgreSQL , . , , .

+3

. t g ALTER TABLE. (, _t, _g), .

, character varying, . , double precision.

, . SQL:

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;

, g :

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;
+2

More articles:


All Articles