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]
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
