PostgreSQL Function - Return a single row from multiple queries

Question

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 ?

Zakaria · Accepted Answer · 2022-03-14 07:35:34Z

3

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;

answered Mar 14, 2022 at 7:35

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

blissweb Over a year ago

Good, and useable but doesn't like if I make one of them have 2 cols like count(*), sum(items) . Anyway to cater for that ?

Zakaria Over a year ago

@blissweb just add another select for the sum.

blissweb Over a year ago

For performance hack, discovered this works ... CONCAT(CAST(sum(noitems) AS VARCHAR(10)) , '-' , CAST(count(*) AS VARCHAR(10))) Maybe someone knows if that will save resources over 2 SELECTS.

RF1991 · Accepted Answer · 2022-03-14 07:54:10Z

2

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

answered Mar 14, 2022 at 7:54

RF1991

2,2634 gold badges12 silver badges20 bronze badges

2 Comments

blissweb Over a year ago

Actually, this seems faster and allows for multiple aggregates on each subquery. Yay !

RF1991 Over a year ago

you can use a up-to-date version of cross join as well, happy to help select * from (select count(*) as acount from tableA) as a cross join (select count(*) as bcount from tableB) as b cross join (select count(*) as ccount from tableC) as c

jian · Accepted Answer · 2022-03-14 07:59:02Z

0

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;

answered Mar 14, 2022 at 7:59

jian

5,0231 gold badge29 silver badges46 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Function - Return a single row from multiple queries

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related