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
signature.asc
Description: PGP signature