Setting a PostgreSQL variable from inside a CASE statement

Question

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:

Work out whether the variable should be 0, or 1.
Set the variable.
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 ":=".

Pavel Stehule · Accepted Answer · 2022-10-26 13:32:02Z

9

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.

edited Oct 26, 2022 at 13:32

answered Sep 30, 2019 at 4:24

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

anakaine Over a year ago

Ok, thank you for such a thorough explanation. I have about 11 million rows, and quite a few things that need calculating - so it's entirely possible that by using derived tables, etc, that I will run into a resource usage and efficiency issue.

Collectives™ on Stack Overflow

Setting a PostgreSQL variable from inside a CASE statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related