Postgres: Create a function to create a new logging table which inherits from another

When creating large logging tables, it better to define the structure of the logging table then create tables that inherit from that table, to put the data into. It makes look-ups on the table faster if you are searching for info in your logs relevant to data in one partitioned child table and it also makes it easier to trim the logs since you can just drop a child table of logs when the time is appropriate.

See http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

 
 
 CREATE OR REPLACE FUNCTION app_system.create_request_log_partition(_date timestamp without time zone)  
  RETURNS void AS  
  $BODY$  
 DECLARE  
      _table text := format('request_log_%s_q%s', date_part('year', _date), date_part('quarter', _date));  
 BEGIN  
        
      EXECUTE  
            'CREATE TABLE system.'|| _table ||'() INHERITS (system.access_log);'||E'\n'  
           ||'CREATE INDEX '|| _table ||'_created_idx ON system.'|| _table ||'(created_at);'||E'\n'  
           ||'CREATE INDEX '|| _table ||'_api_request_idx ON system.'|| _table ||'(api_request);'||E'\n'  
           ||'GRANT INSERT, SELECT ON system.'|| _table ||' TO myapp;'||E'\n'  
           ;  
 END;  
 $BODY$  
 LANGUAGE plpgsql VOLATILE  
 COST 100;  
 ALTER FUNCTION app_system.create_request_log_partition(timestamp without time zone) OWNER TO postgres;