diff options
Diffstat (limited to 'doc/src/sgml/plpgsql.sgml')
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 183 |
1 files changed, 180 insertions, 3 deletions
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index c7f46d613be..3842ee5ef7c 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.55 2005/01/08 22:13:34 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.56 2005/01/14 01:16:22 tgl Exp $ --> <chapter id="plpgsql"> @@ -2306,6 +2306,32 @@ FETCH ALL IN "<unnamed cursor 1>"; COMMIT; </programlisting> </para> + + <para> + The following example shows one way to return multiple cursors + from a single function: + +<programlisting> +CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ +BEGIN + OPEN $1 FOR SELECT * FROM table_1; + RETURN NEXT $1; + OPEN $2 FOR SELECT * FROM table_2; + RETURN NEXT $2; + RETURN; +END; +$$ LANGUAGE plpgsql; + +-- need to be in a transaction to use cursors. +BEGIN; + +SELECT * FROM myfunc('a', 'b'); + +FETCH ALL FROM a; +FETCH ALL FROM b; +COMMIT; +</programlisting> + </para> </sect3> </sect2> </sect1> @@ -2585,8 +2611,6 @@ $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); </programlisting> - - </example> <para> @@ -2646,6 +2670,159 @@ AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); </programlisting> </example> + + <para> + One use of triggers is to maintain a summary table + of another table. The resulting summary can be used in place of the + original table for certain queries — often with vastly reduced run + times. + This technique is commonly used in Data Warehousing, where the tables + of measured or observed data (called fact tables) can be extremely large. + <xref linkend="plpgsql-trigger-summary-example"> shows an example of a + trigger procedure in <application>PL/pgSQL</application> that maintains + a summary table for a fact table in a data warehouse. + </para> + + + <example id="plpgsql-trigger-summary-example"> + <title>A <application>PL/pgSQL</application> Trigger Procedure For Maintaining A Summary Table</title> + + <para> + The schema detailed here is partly based on the <emphasis>Grocery Store + </emphasis> example from <emphasis>The Data Warehouse Toolkit</emphasis> + by Ralph Kimball. + </para> + +<programlisting> +-- +-- Main tables - time dimension and sales fact. +-- +CREATE TABLE time_dimension ( + time_key integer NOT NULL, + day_of_week integer NOT NULL, + day_of_month integer NOT NULL, + month integer NOT NULL, + quarter integer NOT NULL, + year integer NOT NULL +); +CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key); + +CREATE TABLE sales_fact ( + time_key integer NOT NULL, + product_key integer NOT NULL, + store_key integer NOT NULL, + amount_sold numeric(12,2) NOT NULL, + units_sold integer NOT NULL, + amount_cost numeric(12,2) NOT NULL +); +CREATE INDEX sales_fact_time ON sales_fact(time_key); + +-- +-- Summary table - sales by time. +-- +CREATE TABLE sales_summary_bytime ( + time_key integer NOT NULL, + amount_sold numeric(15,2) NOT NULL, + units_sold numeric(12) NOT NULL, + amount_cost numeric(15,2) NOT NULL +); +CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key); + +-- +-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE. +-- +CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$ + DECLARE + delta_time_key integer; + delta_amount_sold numeric(15,2); + delta_units_sold numeric(12); + delta_amount_cost numeric(15,2); + BEGIN + + -- Work out the increment/decrement amount(s). + IF (TG_OP = 'DELETE') THEN + + delta_time_key = OLD.time_key; + delta_amount_sold = -1 * OLD.amount_sold; + delta_units_sold = -1 * OLD.units_sold; + delta_amount_cost = -1 * OLD.amount_cost; + + ELSIF (TG_OP = 'UPDATE') THEN + + -- forbid updates that change the time_key - + -- (probably not too onerous, as DELETE + INSERT is how most + -- changes will be made). + IF ( OLD.time_key != NEW.time_key) THEN + RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key; + END IF; + + delta_time_key = OLD.time_key; + delta_amount_sold = NEW.amount_sold - OLD.amount_sold; + delta_units_sold = NEW.units_sold - OLD.units_sold; + delta_amount_cost = NEW.amount_cost - OLD.amount_cost; + + ELSIF (TG_OP = 'INSERT') THEN + + delta_time_key = NEW.time_key; + delta_amount_sold = NEW.amount_sold; + delta_units_sold = NEW.units_sold; + delta_amount_cost = NEW.amount_cost; + + END IF; + + + -- Update the summary row with the new values. + UPDATE sales_summary_bytime + SET amount_sold = amount_sold + delta_amount_sold, + units_sold = units_sold + delta_units_sold, + amount_cost = amount_cost + delta_amount_cost + WHERE time_key = delta_time_key; + + + -- There might have been no row with this time_key (e.g new data!). + IF (NOT FOUND) THEN + BEGIN + INSERT INTO sales_summary_bytime ( + time_key, + amount_sold, + units_sold, + amount_cost) + SELECT f.time_key, + sum(f.amount_sold), + sum(f.units_sold), + sum(f.amount_cost) + FROM sales_fact f + WHERE f.time_key = delta_time_key + GROUP BY f.time_key; + -- This query can potentially be very expensive if the trigger + -- is created on sales_fact without the time_key indexes. + -- Some care is needed to ensure that this situation does + -- *not* occur. + EXCEPTION + -- + -- Catch race condition when two transactions are adding data + -- for a new time_key. + -- + WHEN UNIQUE_VIOLATION THEN + UPDATE sales_summary_bytime + SET amount_sold = amount_sold + delta_amount_sold, + units_sold = units_sold + delta_units_sold, + amount_cost = amount_cost + delta_amount_cost + WHERE time_key = delta_time_key; + + END; + END IF; + RETURN NULL; + + END; +$maint_sales_summary_bytime$ LANGUAGE plpgsql; + +CREATE TRIGGER maint_sales_summary_bytime +AFTER INSERT OR UPDATE OR DELETE ON sales_fact + FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime(); +</programlisting> + </example> + </sect1> <!-- **** Porting from Oracle PL/SQL **** --> |