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.
niedziela, 24 listopada 2013
wtorek, 12 listopada 2013
Hardware blog
Very interesting blog, posts about CPU/hardware design are worth to read - http://danluu.com/
poniedziałek, 4 listopada 2013
Short story about PostgreSQL SUM function
Here is a simple PostgreSQL type:
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:
Smaller types are promoted: from integer we get bigint, from bigint we get numeric, and so on.
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.