PostgreSQL Function - Return a single row from multiple queries-CodePudding

I would like to write a Postgres function which takes various stats from other tables and combines the results into a single row.

e.g.

select count(*) as acount from tableA
select count(*) as bcount from tableB
select count(*) as ccount from tableC

returning

acount, bcount, ccount   as a single record

Obviously the "count" functions listed above are a lot more complex than that, and may even be different amounts of counts for each table.

So ideally is it possible to store them as variables and then build a record from the variables ?

CodePudding user response：

No need for variables, you can just combine everything into one select statement with subqueries:

select
(select count(*) from tableA) as acount,
(select count(*) from tableB) as bcount,
(select count(*)  from tableC) as ccount;

CodePudding user response：

You can use cross join

select * from
(select count(*) as acount from tableA) as a
,(select count(*) as bcount from tableB) as b
,(select count(*) as ccount from tableC) as c

CodePudding user response：

You can also create a new type that consist of 3 bigint type.

CREATE TYPE mutli_count AS (
    _countt1 bigINT,
    _countt2 bigint,
    _countt3 bigINT
);

select( 
    (select count(*) from country),
    (select count(*) from states),
    (select count(*) from cities))::mutli_count;