Return result of three SQL queries in one SQL view

Question

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.)

Answer 1

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;