Re: partition tree inspection functions

On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:
> I'm thinking, an SQL query might be more efficient if you want to
> qualify the query further. For example, give me all tables in this tree
> that match '2018'. If you wrote your functions as SQL-language
> functions, the optimizer could perhaps inline them and optimize them
> further.

Are you thinking about SQL functions here? Here is an example of query
able to fetch an entire partition tree.
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'your_parent_table_name_here' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition
)
SELECT * FROM partition_info;

Getting the direct parent is immediate, and getting the top-most parent
would be rather similar to that. Not much elegant in my opinion, but
that's mainly a matter of taste?
--
Michael