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 ?
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;