Procedury składowane w PostgreSQL

Książka na wikibooks - opisuje zarządzanie procedurami z punktu widzenia bazy danych, oraz szczegółowo wbudowany język PL/pgSQL.

Dopisek z 2013-12-09: udostępniłem skrypty SQL, które powstały na potrzeby tego podręcznika.

Hardware blog

Very interesting blog, posts about CPU/hardware design are worth to read - http://danluu.com/

Short story about PostgreSQL SUM function

Here is a simple PostgreSQL type:

CREATE TYPE foo_t AS (
 id    integer,
 total bigint
);

and a simple query wrapped in stored procedure:

CREATE FUNCTION group_foo()
 RETURNS SETOF foo_t
 LANGUAGE "SQL"
AS $$
 SELECT id, SUM(some_column) FROM some_table GROUP BY id;
$$;

Now, we want to sum everything:

CREATE FUNCTION total_foo()
 RETURNS bigint -- same as foo_t.total
 LANGUAGE "SQL"
AS $$
 SELECT SUM(total) FROM group_foo();
$$;

And we have an error about type inconsistency!

This is caused by SUM function -- in PostgreSQL there are many variants of this function, as the db engine supports function name overriding (sounds familiar for C++ guys). There are following variants in PostgreSQL 9.1:

$ \df sum
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type 
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | bigint              | agg
 pg_catalog | sum  | double precision | double precision    | agg
 pg_catalog | sum  | bigint           | integer             | agg
 pg_catalog | sum  | interval         | interval            | agg
 pg_catalog | sum  | money            | money               | agg
 pg_catalog | sum  | numeric          | numeric             | agg
 pg_catalog | sum  | real             | real                | agg
 pg_catalog | sum  | bigint           | smallint            | agg

Smaller types are promoted: from integer we get bigint, from bigint we get numeric, and so on.