From a294726bc1dd36e6fb026fdbe4e8db2b7bb4c281 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 14 Jan 2005 01:16:52 +0000 Subject: [PATCH] Incorporate examples and doc patches from Mark Kirkwood and David Fetter. --- doc/src/sgml/plpgsql.sgml | 183 ++++++++++++++++++++++++++++++++++- doc/src/sgml/ref/alter_table.sgml | 13 ++- doc/src/sgml/ref/create_opclass.sgml | 12 ++- 3 files changed, 203 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index c7f46d613b..3842ee5ef7 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -2306,6 +2306,32 @@ FETCH ALL IN "<unnamed cursor 1>"; COMMIT; + + + The following example shows one way to return multiple cursors + from a single function: + + +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; + + @@ -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(); - - @@ -2646,6 +2670,159 @@ AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); + + + 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. + shows an example of a + trigger procedure in PL/pgSQL that maintains + a summary table for a fact table in a data warehouse. + + + + + A <application>PL/pgSQL</application> Trigger Procedure For Maintaining A Summary Table + + + The schema detailed here is partly based on the Grocery Store + example from The Data Warehouse Toolkit + by Ralph Kimball. + + + +-- +-- 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(); + + + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 477ccf99f0..b326804233 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -538,6 +538,17 @@ ALTER TABLE distributors + To change an integer column containing UNIX timestamps to timestamp + with time zone via a USING clause: + +ALTER TABLE foo + ALTER COLUMN foo_timestamp TYPE timestamp with time zone + USING + timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; + + + + To rename an existing column: ALTER TABLE distributors RENAME COLUMN address TO city; diff --git a/doc/src/sgml/ref/create_opclass.sgml b/doc/src/sgml/ref/create_opclass.sgml index 49a0bd74fa..575672371e 100644 --- a/doc/src/sgml/ref/create_opclass.sgml +++ b/doc/src/sgml/ref/create_opclass.sgml @@ -1,5 +1,5 @@ @@ -206,6 +206,16 @@ CREATE OPERATOR CLASS name [ DEFAUL + Notes + + + The operators should not be defined by SQL functions. A SQL function + is likely to be inlined into the calling query, which will prevent + the optimizer from recognizing that the query matches an index. + + + + Examples -- 2.11.0