CODEWITHSUNDEEP
postgresqlplpgsql
anakaine
anakaine

Reputation: 1248

Setting a PostgreSQL variable from inside a CASE statement

Currently using PostgresSQL 10 as a newbie, and following documentation as best I can. All help welcome.

I'm trying to set the value of a variable based on a case statement so I can use that value in the next part of my code. I'm having issues with syntax, and honestly just understanding how to set the value from within the case statement.

Below is the code that I've got so far.

I'm trying to set the value of the variable "AndSol". The value will either bet set at 0, or 1 based upon the case statement.

The block with the comment "Solar Radiation" is where I'm having trouble setting the value of the variable. The rest of the code is included just for reference as to what I'm trying to achieve. I'm ending each where clause with something along the lines of THEN AndSol := 1;, and I assume this is where the error lies. I don't know how to fix it.

Basic flow:

  1. Work out whether the variable should be 0, or 1.

  2. Set the variable.

  3. Use the variable in the next part.

DO $$
DECLARE AndSol NUMERIC;
BEGIN
    DROP MATERIALIZED VIEW IF EXISTS view_6day_mat;
    CREATE MATERIALIZED VIEW view_6day_mat
    AS
        SELECT prod_qld_6km_grids.weather_cell_id,
                prod_qld_6km_grids.latitude,
                prod_qld_6km_grids.longitude,

               /* Solar Radiation */
                CASE
                    WHEN EXTRACT(MONTH FROM prod_weathergrids.dates) >= 8 AND EXTRACT(MONTH FROM prod_weathergrids.dates) <= 12 THEN AndSol := 1;
                    WHEN EXTRACT(MONTH FROM prod_weathergrids.dates) = 1 AND EXTRACT(MONTH FROM prod_weathergrids.dates) <= 2 AND prod_weathergrids.cloudcover < 30 then AndSol := 1;
                ELSE AndSol := 0; END/* 0 END AS AndersonsSolarRad, */

                /* Moisture Content */
                CASE WHEN prod_weathergrids.rh > 0 AND prod_weathergrids.temperature > 0 THEN
                    ROUND(4.37+0.161*prod_weathergrids.rh-0.1*(prod_weathergrids.temperature-25)-AndSol*0.027*prod_weathergrids.rh), 2)
                ELSE 0 END AS AndersonsHMC

                FROM MyTable
    ORDER BY prod_qld_6km_grids.weather_cell_id
END $$;

The error message I receive from pgAdmin is:

ERROR:  syntax error at or near ":="
LINE 49: ...(MONTH FROM prod_weathergrids.dates) <= 12 THEN AndSol := 1;
                                                                   ^
SQL state: 42601
Character: 2440

I must admit, I don't know how to overcome the error, beacuse the documentation and examples I've seen mostly all set the variable by using ":=".

Upvotes: 4

Views: 12455

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

You are using SQL CASE expression. This statement is functional. It does transformation from one value to second, but it cannot to hold any PLpgSQL statements.

PLpgSQL supports procedural CASE with similar syntax, that allows assign statement. But this statements cannot to be nested inside other SQL statements.

DO $$
DECLARE is_monday boolean;
BEGIN
  -- SQL case, functiona
  is_monday = CASE EXTRACT(DOW FROM CURRENT_DATE) 
                WHEN 1 THEN true -- only a value or SQL expression is allowed there 
                ELSE false 
              END;
  RAISE NOTICE 'Today is Monday %', is_monday;

  -- procedural PLpgSQL case
  -- cannot be used inside SQL statement
  CASE EXTRACT(DOW FROM CURRENT_DATE)
    WHEN 1 THEN
      -- nesting PLpgSQL statements
      is_monday := true;
      RAISE NOTICE 'today is Monday';
    ELSE
      is_monday := false;
      RAISE NOTICE 'today is not Monday';
  END CASE; -- ending with "END CASE"
END; $$;

What you want is not possible (not without relative ugly workaround and not only with SQL) in Postgres. When you are calculate column value, you cannot to reuse any information calculated on same level.

You can use derived tables. You can calculate some information deeper, and you can use this information more times on higher levels:

SELECT dt.x, dt.x + 1, dt.x * 10
  FROM (SELECT random() * 10 AS x
          FROM generate_series(1, 10)) dt; -- derived table

Your example showing interesting bug - trap. You are creating view from PLpgSQL. But this view will live without PLpgSQL - so you cannot to use PLpgSQL features there - like variables.

Upvotes: 8

Related Questions