PostgreSQL 9.3 Overview

Transcript

1. PostgreSQL 9.3 overview
2. Schemas SQL CREATE SCHEMA schema; schema.table database.schema.table CREATE TABLE myschema.table ( … ); DROP SCHEMA myschema CASCADE; SHOW search_path; SET search_path TO myschema, public; SHOW search_path; search_path text "$user",public search_path text myschema, public
3. Data types: Usual Numeric Name Storage size Description Range smallint 2 bytes small-range integer -32768 to +32767 integer 4 bytes typical choice for integer -2147483648 to 2147483647 bigint 8 bytes large-range integer -9223372036854775808 to -9223372036854775807 decimal, numeric variable user-specified precision, exact to 131072 digits before the point; up to 16383 digits after the point real 4 bytes variable-precision, inexact 6 decimal digits precision double precision 8 bytes variable-precision, inexact 15 decimal digits precision Char Name Description character varying(n), varchar(n) variable-length with limit character(n), char(n) fixed-length, blank padded text variable unlimited length Temporal Name B Description Low Value High Value Resolution timestamp [(p)] [without time zone] 8 both date and time (no tz) 4713 BC 294276 AD 1 microsec/14 digits timestamp [(p)] with time zone 8 both date and time, with tz 4713 BC 294276 AD 1 microsec/14 digits date 4 date (no time of day) 4713 BC 5874897 AD 1 day time [(p)] [without time zone] 8 time of day (no date) 00:00:00 24:00:00 1 microsec/14 digits time [(p)] with time zone 12 times of day only, with tz 00:00:00+1459 24:00:00-1459 1 microsec/14 digits interval [fields] [(p)] 12 time interval -178000000 years 178000000 years 1 microsec/14 digits Enum Other Type Size Description money 8 currency amount (lc_monetary) boolean 1 true/false bytea 1-* variable length binary string CREATE TYPE status_enum AS ENUM (‘pending’, ‘in_progress’, ‘done’); CREATE TABLE tbl_order (id bigserial, status status_enum); INSERT INTO tbl_order VALUES (1, ‘pending’);
4. Data types: Special Geometric Name Storage size Representation Description point 16 bytes Point on a plane (x,y) line 32 bytes Infinite line (not fully implemented) ( (x1,y1), (x2,y2) ) lseg 32 bytes Finite line segment ( (x1,y1), (x2,y2) ) box 32 bytes Rectangular box ( (x1,y1), (x2,y2) ) path 16 + 16n bytes Closed path (similar to polygon) ( (x1,y1), … ) path 16 + 16n bytes Open path [ (x1,y1), … ] polygon 40 + 16n bytes Polygon (similar to open path) ( (x1,y1), … ) circle 24 bytes Circle < (x,y), r > Network Name B Description cidr 7 or 19 bytes IPv4 and IPv6 networks inet 7 or 19 bytes IPv4 and IPv6 hosts and networks macaddr 6 bytes MAC addresses UUID SELECT a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 Bits Name Size Example bit (n) n B’101’ bit varying (n) n B’1000101’ Name ts_vector ts_query Text Search
5. Data types: Arrays SQL CREATE TABLE test_arrays ( usual integer[], usual_dim integer[4], nested text[][], nested_dim integer[3][3], ansi_sql integer ARRAY, ansi_sql_dim integer ARRAY[4] ); SELECT ‘{1,2,3}’, ARRAY[1,2,3], ‘{ {1,2,3}, {4,5,6} }’, ARRAY[ [1,2,3], [4,5,6] ]; SELECT '{1,2,3}'::int[][1]; -- outputs: 1, since indeces start from 1 SELECT '{1,2,3}'::int[][2:3]; -- result: {2,3} UPDATE my_array SET my_array [4] = 15000; UPDATE my_array SET my_array[1:2] = ‘{1,2}’; SELECT ‘{1,2}’::int[] || ‘{3,4}’::int[]; -- result: {1,2,3,4} SELECT ARRAY[1,4,3] @> ARRAY[3,1]; -- true SELECT unnest(string_to_array(‘a.b’, ‘.’)); --- arrays from query SELECT ARRAY (SELECT * FROM orders WHERE status = ‘pending’); unnest text a b
6. Data types: Composite SQL CREATE TYPE complex AS ( r double precision, i double precision ); CREATE TABLE tbl ( cplx complex ); INSERT INTO tbl VALUES (ROW(1.3, 5.7)); SELECT (cplx).r FROM tbl WHERE (cplx).i > 5.1; SELECT cplx.r FROM tbl WHERE cplx.i > 5.1; -- Invalid! UPDATE tbl SET cplx.r = (cplx).r + 1 WHERE ...; -- though SET part doesn’t need parentheses -- Every table = Composite type SELECT (tbl) FROM tbl; tbl tbl "("(1.3,5.7)")"
7. Data types: Range Types CREATE TABLE reservation (room integer, during tsrange); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); SELECT int4range(10, 20) @> 3; -- Containment: false SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); -- Overlaps: true (@see EXCLUDE) SELECT upper(int8range(15, 25)); -- Extract the upper bound: 25 SELECT int4range(10, 20) * int4range(15, 25); -- Compute the intersection: "[15,20)" SELECT isempty(numrange(1, 5)); -- Is the range empty?: false Name Description int4range Range of integer int8range Range of bigint numrange Range of numeric tsrange Range of timestamp without time zone tstzrange Range of timestamp with time zone daterange Range of date SQL
8. Data types: Pseudo Types Name Description any Indicates that a function accepts any input data type. anyelement Indicates that a function accepts any data type. anyarray Indicates that a function accepts any array data type. anynonarray Indicates that a function accepts any non-array data type. anyenum Indicates that a function accepts any enum data type. anyrange Indicates that a function accepts any range data type. cstring Indicates that a function accepts or returns a null-terminated C string. internal Indicates that a function accepts or returns a server-internal data type. language_handler A procedural language call handler is declared to return language_handler. fdw_handler A foreign-data wrapper handler is declared to return fdw_handler. record dentifies a function returning an unspecified row type. trigger A trigger function is declared to return trigger. void Indicates that a function returns no value. opaque An obsolete type name that formerly served all the above purposes. SQL CREATE FUNCTION twice (me anyelement) RETURNS anyelement AS $$ BEGIN RETURN me * 2; END; $$ LANGUAGE plpgsql IMMUTABLE; SELECT twice(2); -- returns: 2::integer
9. Data types: Object Identifier Types Name References Description Value example oid any numeric object identifier 471367 regproc pg_proc function name sum regprocedure pg_proc function name with argument types sum(integer) regoper pg_operator operator name + regoperator pg_operator operator with argument types *(integer,integer) or – (NONE,integer) regclass pg_class relation name tbl_order regtype pg_type data type name integer regconfig pg_ts_config text search configuration english regdictionary pg_ts_dict text search dictionary simple SQL CREATE OR REPLACE FUNCTION test_func(el anyelement, proc regproc) RETURNS anyelement AS $$ BEGIN EXECUTE 'SELECT ' || proc || '(' || el || ')' INTO el; -- don’t repeat it at home!  RETURN el; END; $$ LANGUAGE plpgsql; SELECT test_func(2, 'twice'::regproc); -- returns: 4
10. Data types: hstore SQL CREATE EXTENSION hstore; SELECT 'a=>1,b=>2'::hstore; SELECT 'a=>1,b=>2'::hstore->'a'; -- key lookup SELECT hstore(ROW(1, 2)); -- "f1"=>"1", "f2"=>"2" UPDATE tbl SET h = h || ('c' => '3'); -- merge hstores UPDATE tbl SET h = delete(h, 'c'); -- delete a key SELECT * FROM each('a=>1,b=>2'); CREATE TABLE test (col1 integer, col2 text, col3 text); SELECT * FROM populate_record(null::test, '"col1"=>"456", "col2"=>"zzz"'); key text value text a 1 b 2 hstore hstore "a"=>"1", "b"=>"2" ?column? text 1 col1 integer col2 text col3 text 456 zzz
11. Data types: XML SQL XMLPARSE ( { DOCUMENT | CONTENT } value) -- Examples (ANSI SQL) XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title></book>') XMLPARSE (CONTENT '<title>Manual</title>') xml '<foo>bar</foo><foo>bar</foo>':: xml -- PostgreSQL Sugar -- Serialize XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type) -- Generate SELECT xmlelement(name foo, xmlattribues('xyz' AS bar)); SELECT xpath('/a/text()', '<a href="http://example.com">test</a>'); -- Convert tables/queries to XML table_to_xml (tbl regclass, nulls boolean, tableforest boolean, targetns text) query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) xmlelement xml <foo bar=“xyz”/> xpath xml[] {test}
12. Data types: JSON JSON operators SELECT array_to_json('{{1,5},{99,100}}'::int[]); -- [ [1,5],[99,100] ] SELECT row_to_json(ROW(1, ‘foo’)); -- {"f1":1,"f2":"foo"} SELECT * FROM json_each('{"a":"foo", "b":"bar"}'); -- hstore -> json SELECT hstore_to_json(‘”a”=>”foo”,”b”=>”bar”’::hstore); key text value json a “foo” b “bar” Operator Right operand type Description Example Result -> int Get JSON array element ‘[1,2,3]’::json->2 3::json -> text Get JSON object field ‘{“a”:1,”b”:2}’::json->’b’ 2::json ->> int Get JSON array element as text '[1,2,3]'::json->>2 3::text ->> text Get JSON object field as text ‘{“a”:1,”b”:2}’::json->>’b’ 2::text #> array of text Get JSON object at specified path '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' 3::json #>> array of text Get JSON object at specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' 3::text SQL hstore_to_json json {"a":"foo", "b":"bar"}
13. Sequences SQL CREATE TABLE tablename ( colname bigserial ); -- is equivalent to: CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( id bigint NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; CREATE [TEMPORARY] SEQUENCE tablename_colname_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 2 CACHE 1 [CYCLE | NO CYCLE]; Function Return type Description currval(regclass) bigint Return value most recently obtained with nextval for specified sequence lastval() bigint Return value most recently obtained with nextval for any sequence nextval(regclass) bigint Advance sequence and return new value setval(regclass, bigint) bigint Set sequence's current value setval(regclass, bigint, boolean) bigint Set sequence's current value and is_called flag Functions Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.
14. Tablespaces SQL CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data'; CREATE TABLE foo(i int) TABLESPACE fastspace; SET default_tablespace = another_space; CREATE TABLE foo(i int); -- created using another_space SELECT spcname FROM pg_tablespace; spcname name pg_default pg_global
15. Tables: DDL SQL CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] --- Examples: CREATE UNLOGGED TABLE tbl_logs ( event text ); -- no write-ahead logging; CREATE TEMP TABLE cache ( key text, val text ) ON COMMIT DROP; -- temp, drops on tx -- From type: CREATE TYPE employee_type AS (name text, salary numeric); CREATE TABLE employees OF employee_type ( PRIMARY KEY (name), salary WITH OPTIONS DEFAULT 1000 -- alters salary column );
16. Constraints & Domains SQL CREATE TABLE IF NOT EXISTS tbl_with_constraints ( id bigserial PRIMARY KEY NOT NULL, -- primary key and not null -- Unique: name text UNIQUE NOT NULL, -- Column check: items bigint NOT NULL CHECK (items > 0), -- Foreign key: user_id bigint NOT NULL REFERENCES users(id) MATCH SIMPLE -- or FULL ON UPDATE CASCADE ON DELETE RESTRICT, -- Table check: CONSTRAINT con1 CHECK (name <> '' AND items < 1000), -- Exclusion: reservation tsrange, EXCLUDE USING gist (c WITH &&) ); -- Domains: CREATE DOMAIN dmn_order_sku AS TEXT CHECK (VALUE ~ '^MS-\d{1,10}$'); CREATE TABLE tbl_orders ( id SERIAL PRIMARY KEY, sku dmn_order_sku NOT NULL -- Domain constraint );
17. Table inheritance (partitioning) SQL CREATE TABLE tbl_base_event ( -- base table id bigserial NOT NULL, namespace text NOT NULL, link_id bigint NOT NULL, message text NOT NULL ); CREATE TABLE tbl_order_event ( -- child table CONSTRAINT pk_ohe PRIMARY KEY (id), CONSTRAINT fk_ohe_order_id FOREIGN KEY (link_id) REFERENCES tbl_order (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT chk_ohe_namespace CHECK (namespace::text = 'order'::text) ) INHERITS (tbl_base_event); INSERT INTO tbl_order_event VALUES (1, 'order', 452, 'created'); SELECT * FROM tbl_base_event; -- OR -- SELECT * FROM tbl_order_event; SELECT * FROM tbl_base_event ONLY; -- returns empty set id bigint namespace text link_id bigint message text 1 order 452 created
18. Indexes Engines -- Operator class: CREATE INDEX idx_bt_my_table_description_varchar_pattern ON my_table USING btree (description varchar_pattern_ops); -- now you can scan with LIKE using index -- Functional indexes: CREATE INDEX idx_featnames_ufullname_varops ON featnames_short USING btree (upper(fullname) varchar_pattern_ops); -- Partial indexes: CREATE TABLE allsubscribers ( id serial PRIMARY KEY, user_name varchar(50) NOT NULL, deactivate timestamptz ); -- Unique names only for active users CREATE UNIQUE INDEX uqidx_1 ON allsubscribers USING btree (lower(user_name)) WHERE deactivate IS NULL; Name Description B-tree general purpose, PK & UNIQUE => only b-tree GiST Generalized Search Tree, full text search, spatial data, astronomical data, hierarchical data + exclusion constraints GIN Generalized Inverted Index, full text search, trigram extensions, SP-GiST Space-Partitioning Trees Generalized Search Tree, faster GiST for certain types, support for geometric types extensions btree_gist, btree_gin, etc. SQL
19. Window functions: Intro SELECT id, cost, delivery, AVG(cost) OVER (PARTITION BY delivery) AS delivery_avg -- agg as window function FROM tbl_order WHERE date = DATE(CURRENT_TIMESTAMP); -- no GROUP BY clause SQL id bigint cost numeric delivery text delivery_avg numeric 4516 2346.50 b2cpl 4589.23 4576 8300.00 dhl 6392.23 Function Return Type Description row_number() bigint number of the current row within its partition, counting from 1 rank() bigint rank of the current row with gaps; same as row_number of its first peer dense_rank() bigint rank of the current row without gaps; this function counts peer groups percent_rank() double precision relative rank of the current row: (rank - 1) / (total rows - 1) cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows) ntile(num_buckets) integer integer ranging from 1 to the argument value, dividing the partition as equally as possible lag(value any [, offset integer [, default any ]]) type of value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null lead(value any [, offset integer [, default any ]]) type of value vice versa first_value(value any) type of value returns value evaluated at the row that is the first row of the window frame last_value(value any) type of value returns value evaluated at the row that is the last row of the window frame nth_value(value any, nth integer) type of value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row Any agg + functions
20. Window functions: Example -- Given we have a table: CREATE TABLE tbl_user (id serial, name text, role text); -- And we perform a query: SELECT id, lag(name) OVER w AS prev_user, -- window function over frame name AS "curr_user", lead(name) OVER w AS next_user, "role", COUNT(*) OVER (PARTITION BY "role") AS users_in_role, -- agg over frame rank() OVER (PARTITION BY "role" ORDER BY id ASC) AS rank_in_role, FROM tbl_user WINDOW w AS (ORDER BY id ASC) -- w is a window frame alias ORDER BY id; -- Given table: -- Query result: SQL id int name text role text 1 alice admin 2 bob user 3 john admin 4 sam user 5 ole user 6 ann user id int prev_user text curr_user text next_user text role text users_in_role int rank_in_role int 1 alice bob admin 2 1 2 alice bob john user 4 1 3 bob john samn admin 2 2 4 john sam ole user 4 2 5 sam ole ann user 4 3 6 ole ann user 4 4
21. Common Table Expressions -- Standart CTE: WITH users_in_role AS ( SELECT "role", COUNT(*) AS total_users FROM tbl_user GROUP BY "role“ ) SELECT id, email, "role", total_users FROM tbl_user INNER JOIN users_in_role USING ("role"); -- Writeable CTE: t1 AS (DELETE FROM ONLY logs_2011 WHERE log_ts < '2011-03-01' RETURNING *) INSERT INTO logs_2011_01_02 SELECT * FROM t1; SQL id int name text role text total_users int 1 alice admin 2 2 bob user 4 3 john admin 2 4 sam user 4 5 ole user 4 6 ann user 4
22. CTE: Upsert -- Given we have a table CREATE TABLE tbl ( delivery text, key text, val text, UNIQUE(delivery, key) ); -- Upsert: LOCK TABLE tbl IN SHARE ROW EXCLUSIVE MODE; WITH new_values ( "delivery", "key", "val" ) AS ( -- dynamic type definition VALUES ('dhl', 'url', 'dhl.com') -- dynamic rows definition ), upsert AS ( UPDATE tbl m SET "val" = nv."val" FROM new_values nv WHERE (m."delivery", m."key") = (nv."delivery", nv."key") -- row comparison RETURNING m.* -- writeable CTE ) INSERT INTO tbl ("delivery", "key", "val") SELECT "delivery", "key", "value" FROM new_values WHERE NOT EXISTS ( SELECT 1 FROM upsert AS up WHERE (up."delivery", up."key") = (new_values."delivery", new_values."key") ); SQL
23. CTE: Recursive WITH RECURSIVE t(n) AS ( -- initial part: VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) UNION ALL -- recursive part: SELECT n+1, (n+1)*2, (n+1)*3, (n+1)*4, (n+1)*5, (n+1)*6, (n+1)*7, (n+1)*8, (n+1)*9, (n+1)*10 FROM t WHERE n < 10 ) SELECT * FROM t; SQL n int column2 int column3 int column4 int column5 int column6 int column7 int column8 int column9 int column10 int 1 2 3 4 5 6 7 8 9 10 2 4 6 8 10 12 14 16 18 20 3 6 9 12 15 18 21 24 27 30 4 8 12 16 20 24 28 32 36 40 5 10 15 20 25 30 35 40 45 50 6 12 18 24 30 36 42 48 54 60 7 14 21 28 35 42 49 56 63 70 8 16 24 32 40 48 56 64 72 80 9 18 27 36 45 54 63 72 81 90 10 20 30 40 50 60 70 80 90 100
24. CTE: Recursive (Adjacency List) CREATE TABLE tree (id serial, name text, parent_id int); -- given we have a table -- And we query: WITH RECURSIVE tree_rec AS ( SELECT id, name, parent_id, '' AS path, '+' AS x, '' AS mp FROM tree WHERE parent_id IS NULL UNION ALL SELECT tree_iter.id, tree_iter.name, tree_iter.parent_id, (tree_rec.path || '| ') AS path, (path || '|-- + ' || tree_iter.name) AS x, (tree_rec.mp || '/' || tree_iter.parent_id || '.' || tree_iter.id) AS mp FROM tree AS tree_iter INNER JOIN tree_rec ON tree_iter.parent_id = tree_rec.id -- recursive join! ) SELECT repeat(' ', strpos(x, '|') - 1) || substring(x FROM strpos(x, '|')) FROM tree_rec ORDER BY mp; -- Given table: -- Query result: SQL id int name text parent_id int 1 root NULL 2 a 1 3 ab 2 4 b 1 5 bc 4 6 bcd 5 ?column? text + |--+ a | |--+ ab |--+ b | |--+ bc | | |--+ bcd
25. Functions: Intro -- SQL: CREATE OR REPLACE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE -- IMMUTABLE |VOLATILE | STABLE STRICT; -- STRICT | CALLED ON NULL INPUT -- PL/PGSQL: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ RETURN CASE WHEN uppercase THEN upper(a || ' ' || b) ELSE lower(a || ' ' || b) END; $$ LANGUAGE plgpsql IMMUTABLE STRICT; -- Invocations: SELECT concat_lower_or_upper('Hello', 'World', true); -- positional args SELECT concat_lower_or_upper(a := 'Hello', b := 'World'); -- named args SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World'); -- named args SELECT concat_lower_or_upper('Hello', 'World', uppercase := true); -- mixed SQL
26. Functions: Example CREATE OR REPLACE FUNCTION order_txs(i_order_id bigint) RETURNS hstore AS $$ DECLARE h hstore; DECLARE r RECORD; BEGIN h = hstore(ARRAY[]::text[]); -- empty hstore FOR r IN SELECT "type", SUM(amount) AS amount FROM tbl_txs WHERE order_id = i_order_id GROUP BY "type" LOOP h = h || hstore(r.type, r.amount::text); END LOOP; RETURN h; $$ LANGUAGE plgsql VOLATILE; -- Example: SELECT id, order_txs(id) AS txs FROM tbl_order WHERE id IN (21356, 46724); SQL id bigint txs hstore 21356 "ds_pfr" => "2748.56", "pay_cost" => "2748.56" 46724 "service_sms" => "6.00"
27. Functions: DO DO $$ DECLARE r RECORD; DECLARE txs hstore; DECLARE s numeric; BEGIN FOR r IN SELECT id FROM tbl_order LOOP txs = order_txs(r.id); s = txs->'ds_pfr'::numeric + txs->'ds_cost'::numeric; RAISE NOTICE 'Order % -> %', r.id, s; UPDATE tbl_order SET debts = s WHERE id = r.id; END LOOP; END; $$ LANGUAGE plgsql; -- Output: -- Order 24677 -> 346.56 -- Order 5677 -> 289.45 -- … SQL
28. Functions: JavaScript CREATE EXTENSION plv8; -- we need an extension -- Define a function: CREATE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $$ var o = {}; for(var i=0; i<keys.length; i++){ o[keys[i]] = vals[i]; } return JSON.stringify(o); $$ LANGUAGE plv8 IMMUTABLE STRICT; -- Invoke it: SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']); SQL ?column? text {"name":"Tom","age":"29"}
29. Functions: Triggers -- Given we have an orders table: CREATE TABLE tbl_order (id bigserial, …); -- And a log table: CREATE TABLE tbl_order_history_event ( order_id bigint, data json ); -- And we want to log insertions/updates on each particular order: CREATE FUNCTION log_order_event() RETURNS trigger AS $$ BEGIN INSERT INTO tbl_order_history_event VALUES (NEW.id, to_json(data)); RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger: CREATE TRIGGER trigger_log_order_event -- trigger name AFTER -- BEFORE | INSTEAD OF | AFTER INSERT OR UPDATE ON tbl_order -- INSERT|UPDATE|DELETE FOR EACH ROW – ROW|STATEMENT EXECUTE PROCEDURE log_order_event(); SQL
30. Custom operators -- Define operator function: CREATE OR REPLACE FUNCTION fn_order_has_tx(i_order_id bigint, i_tx_type text) RETURNS boolean AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM tbl_order_money_transaction WHERE order_id = i_order_id AND "type" = i_tx_type ); END; $$ LANGUAGE plpgsql; -- Define operator: CREATE OPERATOR @? ( LEFTARG = bigint, RIGHTARG = text, PROCEDURE = fn_order_has_tx ); -- Use it: SELECT COUNT(*) FROM tbl_order WHERE id @? 'ds_payment_from_recipient'; SQL COUNT(*) int 42185
31. Custom aggregates -- Define state function: CREATE OR REPLACE FUNCTION geom_mean_state(prev numeric[2], next numeric) RETURNS numeric[2] AS $$ SELECT CASE WHEN $2 IS NULL OR $2 = 0 THEN $1 ELSE ARRAY[coalesce($1[1],0) + ln($2), $1[2] + 1] END; $$ LANGUAGE sql IMMUTABLE; -- Define final function: CREATE OR REPLACE FUNCTION geom_mean_final(numeric[2]) RETURNS numeric AS $$ SELECT CASE WHEN $1[2] > 0 THEN exp($1[1]/$1[2]) ELSE 0 END; $$ LANGUAGE sql IMMUTABLE; -- Create aggregate: CREATE AGGREGATE geom_mean(numeric) ( SFUNC=geom_mean_state, STYPE=numeric[], FINALFUNC=geom_mean_final, INITCOND='{0,0}'); -- Use it: SELECT geom_mean(val) FROM ( VALUES(2), (5), (3) ) AS t (val); SQL ?column? numeric 3.10723250595385889234
32. Foreign Data Wrappers Types SQL postgres_fdw oracle_fdw mysql_fdw odbc_fdw jdbc_fdw informix_fdw firebird_fdw sqlite_fdw tds_fdw NoSQL couchdb_fdw MonetDB_fdw mongo_fdw redis_fdw Neo4j fdw Tycoon fdw File file_fdw file_text_array_fdw file_fixed_length_record_fdw json_fdw Other twitter_fdw ldap_fdw PGStorm Hadoop fdw s3_fdw www_fdw cstore_fdw
33. FDW: File example CREATE EXTENSION file_textarray_fdw; -- install extension if needed CREATE SERVER file_tafdw_server FOREIGN DATA WRAPPER file_textarray_fdw; -- User mapping (not always necessary): CREATE USER MAPPING FOR public SERVER file_tafdw_server; CREATE FOREIGN TABLE fgn_tbl_delivery( x text[] ) SERVER file_tafdw_server OPTIONS (filename '/data.csv', encoding 'utf8', delimiter ':'); --Query: SELECT x[1] AS delivery, x[2] AS origin_id FROM fgn_tbl_delivery WHERE x[2]::int < 1000; SQL Given we have a file /data.csv with following contents: axiomus:4 dhl:10 b2cpl:3967 delivery text origin_id text axiomus 4 dhl 10
34. Materialized Views -- Create materialized view from query; CREATE MATERIALIZED VIEW vw_orders_with_txs AS SELECT *, order_txs(id) AS order_txs FROM tbl_order; SELECT * FROM vw_orders_with_txs WHERE order_txs->'ds_pfr'::numeric > 1000; -- Though you have to refresh it manually: REFRESH MATERIALIZED VIEW vw_orders_with_txs; -- Create materialized view from foreign table: CREATE EXTENSION file_fdw; CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE words (word text NOT NULL) SERVER local_file OPTIONS (filename '/etc/dictionaries-common/words'); CREATE MATERIALIZED VIEW vw_words AS SELECT * FROM words; CREATE UNIQUE INDEX wrd_word ON vw_words (word); CREATE EXTENSION pg_trgm; CREATE INDEX wrd_trgm ON vw_words USING gist (word gist_trgm_ops); VACUUM ANALYZE vw_words; SELECT COUNT(*) FROM vw_words WHERE word = 'caterpiler'; SQL
35. Misc: COPY PROGRAM -- Copy TO external program: COPY (SELECT 1, 2) TO PROGRAM 'sed -e "s/,/:/" > ~/test.txt' DELIMITER ','; -- COPY FROM external program: CREATE TABLE weather_json (cities json); COPY weather_json FROM PROGRAM 'curl http://api.openweathermap.org/data/2.5/weather?q=Tokyo'; SELECT cities->'name' FROM weather_json; SQL $ cat ~/test.txt 1:2 ?column? text "Tokyo"
36. Postgres-specific features -- Shorthand casting using :: SELECT '1.2'::numeric; /* SAME AS */ SELECT CAST('1.2' AS numeric); -- DISTINCT ON SELECT DISTINCT ON (left(origin_id, 5)) id, name, left(origin_id, 5) FROM tbl_order WHERE delivery = 'dhl'; -- Case insensitive LIKE SELECT * FROM tbl_order WHERE delivery ILIKE '%hl'; -- Set Returning Functions in SELECT -- Complex types in queries: SELECT n, generate_series(1, n) SELECT tree FROM tree LIMIT 4; FROM ( VALUES(1), (2) ) AS tbl (n); -- RETURNING changed records: UPDATE tbl_order SET status = 'error' WHERE status = 'unknown' RETURNING id; -- returns set of records SQL id int generate_series n 1 1 2 1 2 2 tree tree (1,root,) (2,a,1) (3,ab,2) (4,b,1)
37. Resources Resources Name Url Documentation http://www.postgresql.org/docs/9.3/interactive/index.html Postgresql wiki http://wiki.postgresql.org/wiki/Main_Page Posgresql online journal http://www.postgresonline.com Books Title PostgreSQL: Up and Running PostgreSQL: Introduction And Concepts
38. THE END aveic@mail.ru