postgresqlwith-statementdeclare

How can wrap the expression with declare or with clause in psql?


Using a psql variable to store a expression in set:

\set vix round(100*(high/nullif(low,0)-1),2)

Call the variable in select :

select  :vix as vix from quote 
    where date='2023-12-08'  and :vix is not null 
    order by :vix desc  
    limit 10;

It print the output such as below:

   vix   
---------
 1466.53
  502.94
  167.57
  163.67
  150.00
  150.00
  141.13
  133.33
  105.58
  100.00
(10 rows)

Call the variable vix in declare:

DO $$
    DECLARE vix float := round(100*(high/nullif(low,0)-1),2);
BEGIN
    select  vix as vix from quote 
    where date='2023-12-08'  and vix is not null 
    order by vix desc  
    limit 10;
END $$;

It encouter error:

ERROR:  column "high" does not exist
LINE 1: SELECT round(100*(high/nullif(low,0)-1),2)
                          ^
QUERY:  SELECT round(100*(high/nullif(low,0)-1),2)
CONTEXT:  PL/pgSQL function inline_code_block line 3 during statement block local variable initialization
Time: 0.604 ms

Put it in with clause:

WITH  vix as (
   round(100*(high/nullif(low,0)-1),2)
)
select  vix as vix from quote 
where date='2023-12-08'  and vix is not null 
order by vix desc  
limit 10;

It encounter error:

ERROR:  syntax error at or near "round"
LINE 2:    round(100*(high/nullif(low,0)-1),2)

How can fix them ?


Solution

  • I think what you want is a subquery to add a "virtual" column:

    WITH temp as (
      SELECT *, round(100*(high/nullif(low,0)-1),2) AS vix
      FROM quote
    )
    SELECT *
    FROM temp
    WHERE date = '2023-12-08'
      AND vix IS NOT NULL
    ORDER BY vix DESC
    LIMIT 10;
    

    or

    SELECT *
    FROM (
      SELECT *, round(100*(high/nullif(low,0)-1),2) AS vix
      FROM quote
    ) AS temp
    WHERE date = '2023-12-08'
      AND vix IS NOT NULL
    ORDER BY vix DESC
    LIMIT 10;