Thread: Intermediate values and unprivileged users

My apologies if this is an FAQ or considered too general.

I have a query like this which returns a single result:

SELECT (
   (SELECT avg(rel_pressure) as avg4
     FROM weather
     WHERE now() - datetime <= '4 hours'
   ) -
   (SELECT avg(rel_pressure) as avg24
     FROM weather
     WHERE now() - datetime <= '24 hours'
   )
) AS diff;

What I want to be able to do is have a slightly more complex query like
this:

SELECT (
   (SELECT avg(rel_pressure) AS avg4
     FROM weather
     WHERE now() - datetime <= '4 hours'
   ) -
   (SELECT avg(rel_pressure) AS avg24
     FROM weather
     WHERE now() - datetime <= '24 hours'
   )
) AS diff,
CASE
   WHEN diff < -0.1 THEN 'Falling'
   WHEN diff > 0.1 THEN 'Rising'
   ELSE 'Stable'
END AS tendency;

i.e. the result should be a single row with two columns. Unfortunately
all my attempts so far tell me that column "diff" does not exist.

Now in most cases I could wing it using a view or temporary table, but
in the current one users of the database will not have creation rights:
they have to get their queries right or (eventually) use a high-level
language.

Is there a "good" way to get round this?

Server is 8.2 on Linux x86, queries from psql.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

Use subselect...


SELECT f.foo, (CASE WHEN f.foo > 'something' THEN ...) FROM ( SELECT .... ) f;