On 6/28/18 13:30, Michael Paquier wrote: > 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' [...]
Yes, this kind of thing should be more efficient than building the entire tree in a C function and then filtering it afterwards. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
