<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >A Table Rewrite Event Trigger Example</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.6.4 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Event Triggers" HREF="event-triggers.html"><LINK REL="PREVIOUS" TITLE="A Complete Event Trigger Example" HREF="event-trigger-example.html"><LINK REL="NEXT" TITLE="The Rule System" HREF="rules.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2017-08-11T02:27:18"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="4" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.6.4 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="A Complete Event Trigger Example" HREF="event-trigger-example.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="event-triggers.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 38. Event Triggers</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="The Rule System" HREF="rules.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="EVENT-TRIGGER-TABLE-REWRITE-EXAMPLE" >38.5. A Table Rewrite Event Trigger Example</A ></H1 ><P > Thanks to the <TT CLASS="LITERAL" >table_rewrite</TT > event, it is possible to implement a table rewriting policy only allowing the rewrite in maintenance windows. </P ><P > Here's an example implementing such a policy. </P><PRE CLASS="PROGRAMLISTING" >CREATE OR REPLACE FUNCTION no_rewrite() RETURNS event_trigger LANGUAGE plpgsql AS $$ --- --- Implement local Table Rewriting policy: --- public.foo is not allowed rewriting, ever --- other tables are only allowed rewriting between 1am and 6am --- unless they have more than 100 blocks --- DECLARE table_oid oid := pg_event_trigger_table_rewrite_oid(); current_hour integer := extract('hour' from current_time); pages integer; max_pages integer := 100; BEGIN IF pg_event_trigger_table_rewrite_oid() = 'public.foo'::regclass THEN RAISE EXCEPTION 'you''re not allowed to rewrite the table %', table_oid::regclass; END IF; SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid; IF pages > max_pages THEN RAISE EXCEPTION 'rewrites only allowed for table with less than % pages', max_pages; END IF; IF current_hour NOT BETWEEN 1 AND 6 THEN RAISE EXCEPTION 'rewrites only allowed between 1am and 6am'; END IF; END; $$; CREATE EVENT TRIGGER no_rewrite_allowed ON table_rewrite EXECUTE PROCEDURE no_rewrite();</PRE ><P> </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="event-trigger-example.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="rules.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >A Complete Event Trigger Example</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="event-triggers.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >The Rule System</TD ></TR ></TABLE ></DIV ></BODY ></HTML >