PostgreSQL 9.3

Piotr Woszczyk

04.2014

RELEASE 9.3

materialized views
auto-updatable views
features for the JSON
LATERAL
event triggers
non-key-field problem
reduce shared memory requirements

materialized views

Tworzenie zbliżone do standardowego widoku:

 CREATE MATERIALIZED VIEW table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

Łatwy do odświeżenia bez przebudowywania:

REFRESH MATERIALIZED VIEW name
    [ WITH [ NO ] DATA ]

auto-updatable views

Posiadają jedną table w klauzurze FROM
Nie posiadają słów kluczowych
WITH, DISTINCT, GROUP BY,
HAVING, LIMIT, OFFSET
w głównej definicji.
Nie są tworzone za pomocą
UNION, INTERSECT, EXCEPT
Nie są tworzone za pomocą funkcji agregujących
Nie mają włączonego security_barrier

AUTO-UPDATABLE VIEWS

Problem nie pojawiających się danych

 CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

 INSERT INTO comedies (kind) VALUES ('Thriller');

Rozwiązanie za pomocą CHECK OPTION

 CREATE VIEW universal_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'U'
    WITH LOCAL CHECK OPTION;

features for the JSON

Operatory:

'{"a":1,"b":2}'::json->'b' @return object
'{"a":1,"b":2}'::json->>'b' @return text
'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}' @return object
'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' @return text

FEATURES FOR THE JSON

array_to_json(anyarray [, pretty_bool]), od 9.2
row_to_json(record [, pretty_bool]), od 9.2
to_json(anyelement)
json_array_length(json)
json_each(json)
json_each_text(from_json json)
json_extract_path(from_json json, VARIADIC path_elems text[])
json_extract_path_text(from_json json, VARIADIC path_elems text[])
json_object_keys(json)
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]
json_array_elements(json)

LATERAL

Opóźnia dodanie podzapytania do klauzuli FROM, umożliwiając wykonanie szybkiego złączania każdy z każdym.

 SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

zastępuje

 SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

event triggers

Możliwe jest obserwowanie operacji wykonywanych na strukturze bazy danych.

 CREATE EVENT TRIGGER name
  ON event
  [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
  EXECUTE PROCEDURE function_name()

Dostępne jest kilka wartości elementu event.

ddl_command_start
ddl_command_end
sql_drop

EVENT TRIGGERS

Przykładowe zastosowanie.

 CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
   EXECUTE PROCEDURE abort_any_command();

NON-KEY-FIELD PROBLEM

 CREATE TABLE foo (
    id        integer CONSTRAINT PRIMARY KEY,
    title     text
);

 CREATE TABLE bar (
    id        integer CONSTRAINT PRIMARY KEY,
    id_foo    integer,
    title     text
);

 ALTER TABLE bar     ADD CONSTRAINT bar_fk1 FOREIGN KEY (id_foo) REFERENCES foo (id);

 CREATE TRIGGER check_update
    BEFORE UPDATE ON foo
    FOR EACH ROW
    EXECUTE PROCEDURE check_update_chack();

SOLVED!

THE END

Dziękuję za uwagę.

PostgreSQL

By Piotr Woszczyk