CODEWITHSUNDEEP
postgresqlwith-statementdeclare
showkey
showkey

Reputation: 310

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 ?

Upvotes: 0

Views: 67

Answers (1)

Bergi
Bergi

Reputation: 665276

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;

Upvotes: 1

Related Questions