Running postgresql function for each row in other select returning the combined result

Question

I would like to run the following query on my database:

SELECT nspname || '.' || relname AS relation,
  pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size_pretty, pg_total_relation_size(C.oid) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'

Then for each row in the result run:

SELECT pg_size_pretty(dead_tuple_len + free_space) AS recoverable_space_pretty, (dead_tuple_len + free_space) AS recoverable_space FROM extensions.pgstattuple(relation);

How can I do that in Postgresql returning a result combining the two so I for each row get: relation, total_size_pretty, total_size, recoverable_space_pretty, recoverable_space?

BONUS: I would also like to output the result as CSV

Can you elaborate a bit on the relation between these two queries? I mean, how the columns connect with each other. A simple CTE with a COPY statement should suffice. I can post a generic solution — Jim Jones
– Jim Jones, Commented May 17, 2018 at 8:48
The second query uses relation from each row in first query within the FROM extensions.pgstattuple(relation). So in other words, for each row I will query two more columns based on relation — Niels Kristian
– Niels Kristian, Commented May 17, 2018 at 8:53
Can you check if this here works for you? Since I don't have all your tables I cannot check myself :) COPY ( WITH j AS ( SELECT nspname || '.' || relname AS relation, pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size_pretty, pg_total_relation_size(C.oid) AS total_size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast') SELECT extensions.pgstattuple(j.relation) FROM j ) TO STDOUT CSV HEADER; Hopefully something we can work on :D — Jim Jones
– Jim Jones, Commented May 17, 2018 at 9:03
This is the logic I thought about: COPY ( WITH j AS ( SELECT 1 AS q1 ) SELECT j.q1+1 AS q2 FROM j) TO STDOUT CSV HEADER; — Jim Jones
– Jim Jones, Commented May 17, 2018 at 9:06
Hmm looks like it isn't working since some of the internal queries of the functions relies on views: ERROR: "pg_buffercache" (view) is not supported — Niels Kristian
– Niels Kristian, Commented May 17, 2018 at 9:29

Jim Jones · Accepted Answer · 2018-05-17 09:56:55Z

1

A combination of CTE and COPY should do:

COPY (
WITH j AS (
SELECT nspname || '.' || relname AS relation,
  pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size_pretty, pg_total_relation_size(C.oid) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
)
SELECT pg_size_pretty((pgstattuple(j.relation)).dead_tuple_len + (pgstattuple(j.relation)).free_space) AS recoverable_space_pretty, (
                      (pgstattuple(j.relation)).dead_tuple_len + (pgstattuple(j.relation)).free_space) AS recoverable_space 
FROM j
) TO STDOUT CSV HEADER;

query returned copy data:

recoverable_space_pretty,recoverable_space
8104 bytes,8104
7508 bytes,7508
7992 bytes,7992
8044 bytes,8044
0 bytes,0
5068 bytes,5068
0 bytes,0
0 bytes,0
8104 bytes,8104
7724 bytes,7724
0 bytes,0
0 bytes,0
0 bytes,0

Note: In case you can afford writing on the database server file system, change the STDOUT to a path of your choice.

answered May 17, 2018 at 9:56

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

1 Comment

Niels Kristian Over a year ago

Thanks! Works well

Collectives™ on Stack Overflow

Running postgresql function for each row in other select returning the combined result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related