Thank you, Greg! I tweaked your function to use recursion to search all
inherited tables; my inheritance structure is two levels deep.
This function is for integers only; I will copy/waste to create one for
timestamps. Extra credit for anyone who can rewrite it to be polymorphic.
-- Same as max(_colname) from _relname but much faster for inherited
-- tables with an index on _colname. In postgresql-8.3.6 a naive query
-- on a parent table will not use the indexes on the child tables.
create or replace function partition_max_int(_relname text, _colname text)
returns int AS
$$
declare
childtable RECORD;
childres RECORD;
maxval int;
tmpval int;
sql text;
begin
-- find max in this table (only)
sql := 'select max('||_colname||') from only '||quote_ident(_relname);
execute sql into maxval;
-- recurse to find max in descendants
FOR childtable in
select pc.relname as relname
from pg_class pc
join pg_inherits pi on pc.oid=pi.inhrelid
where inhparent=(select oid from pg_class where relname=_relname)
LOOP
tmpval := partition_max_int(childtable.relname, _colname);
IF tmpval is not NULL and (tmpval > maxval or maxval is null) THEN
maxval := tmpval;
END IF;
END LOOP;
return maxval;
end;
$$
language 'plpgsql' STABLE;
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
