'PostgreSQL Function - Return a single row from multiple queries

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 ?

sqlpostgresqlfunction


Solution 1:[1]

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

Solution 2:[2]

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;

Solution 3:[3]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RF1991
Solution 2 Zakaria
Solution 3 Mark

Related Questions