<!--

$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">

FETCH ALL IN "&lt;unnamed cursor 1&gt;";

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>

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

</programlisting>

</example>

<para>

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 &mdash; 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 : % -&gt; % 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 **** -->

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.76 2005/01/10 00:04:43 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.77 2005/01/14 01:16:52 tgl Exp $

PostgreSQL documentation

-->

</programlisting>

</para>

<para>

To change an integer column containing UNIX timestamps to <type>timestamp

with time zone</type> via a <literal>USING</literal> clause:

<programlisting>

ALTER TABLE foo

ALTER COLUMN foo_timestamp TYPE timestamp with time zone

USING

timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

</programlisting>

</para>

<para>

To rename an existing column:

<programlisting>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_opclass.sgml,v 1.12 2003/11/29 19:51:38 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_opclass.sgml,v 1.13 2005/01/14 01:16:52 tgl Exp $

PostgreSQL documentation

-->

</para>

</refsect1>

<refsect1>

<title>Notes</title>

<para>

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.

</para>

</refsect1>

<refsect1>

<title>Examples</title>