[SOLVED] Return result of three SQL queries in one SQL view

Return result of three SQL queries in one SQL view

Is it possible to return results of three separate SQL queries in one view?

I would like to create an "Initial data" view, with some stats from my DB, returning three counts() from different tables. Something like that:

CREATE VIEW initial AS
   SELECT count(*) AS albums_count FROM albums,
   SELECT count(*) AS artists_count FROM artists,
   SELECT count(*) AS tracks_count FROM tracks;

I do not mind if the results are in rows or columns. UNION would kind of work - but it does not make sense from performance perspective.

(I know I could just do three separate requests from the frontend or have that combined in my backend code but I use PostgREST as my API, and I do not want to make 3 separate "initial" requests when my website loads.)

Solution

You can union the counts with adding a column for the count type for each, such as

   SELECT count(*) as Quantity, 'albums' as countType FROM albums union all
   SELECT count(*), 'artists' FROM artists union all
   SELECT count(*), 'tracks' FROM tracks;

If you want 3 columns you can just select them as derived tables:

select
(SELECT count(*) FROM albums) AS albums_count, 
(SELECT count(*) FROM artists) AS artists_count,
(SELECT count(*) FROM tracks) AS tracks_count;