11 Enhancement of SQL Statement

postgres=> UPDATE pgbench_tellers SET (bid, tbalance) = ROW (2, 1) WHERE
tid = 10;
UPDATE 1

postgres=> CREATE TABLE stat1(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO stat1 VALUES(generate_series(1, 100000) / 5,
generate_series(1, 100000) / 10, 'init') ;
INSERT 0 100000
postgres=> CREATE STATISTICS stat1_stat1 ON c1, c2 FROM stat1 ;
CREATE STATISTICS

postgres=> \d stat1
                      Table "public.stat1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | numeric               |           |          |
 c3     | character varying(10) |           |          |
Statistics objects:
    "public"."stat1_stat1" (ndistinct, dependencies) ON c1, c2 FROM stat1

Information of the extended statistics can be checked in the pg_statistic_ext catalog.

postgres=> SELECT * FROM pg_statistic_ext ;
-[ RECORD 1 ]---+-----------------------------------------
stxrelid        | 16575
stxname         | stat1_stat1
stxnamespace    | 2200
stxowner        | 16454
stxkeys         | 1 2
stxkind         | {d,f}
stxndistinct    | {"1, 2": 19982}
stxdependencies | {"1 => 2": 1.000000, "2 => 1": 0.170467}

postgres=> CREATE TABLE ident1 (c1 bigint GENERATED ALWAYS AS IDENTITY, c2 VARCHAR(10)) ;
CREATE TABLE
demodb=> \d ident1
                                Table "public.ident1"
 Column |         Type          | Collation | Nullable |           Default
--------+-----------------------+-----------+----------+------------------------------
 c1     | bigint                |           | not null | generated always as identity
 c2     | character varying(10) |           |          |
postgres=> INSERT INTO ident1(c1, c2) VALUES (1, 'data1') ;
ERROR:  cannot insert into column "c1"
DETAIL:  Column "c1" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
postgres=> INSERT INTO ident1(c2) VALUES ('data1') ;
INSERT 0 1
postgres=> UPDATE ident1 SET c1=2 WHERE c1=1 ;
ERROR:  column "c1" can only be updated to DEFAULT
DETAIL:  Column "c1" is an identity column defined as GENERATED ALWAYS.
postgres=> UPDATE ident1 SET c1=DEFAULT WHERE c1=1 ;
UPDATE 1

Any value can be stored on GENERATED column by specifying the OVERRIDING SYSTEM
VALUE clause in the INSERT statement.

postgres=> INSERT INTO ident1 OVERRIDING SYSTEM VALUE VALUES (100, 'data1') ;
INSERT 0 1

postgres=>  CREATE TABLE ident2 (c1 bigint GENERATED BY DEFAULT AS
IDENTITY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO ident2 VALUES (1, 'data1') ;
INSERT 0 1
postgres=> INSERT INTO ident2(c2) VALUES ('data2') ;
INSERT 0 1
postgres=> UPDATE ident2 SET c1=2 WHERE c2='data2' ;
UPDATE 1

postgres=> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') ;
CREATE TYPE
postgres=> ALTER TYPE mood RENAME VALUE 'ok' TO 'good' ;
ALTER TYPE

postgres=> CREATE SEQUENCE seq1 AS SMALLINT ;
CREATE SEQUENCE

It is also possible to change the data type with the ALTER SEQUENCE AS statement. If the data
type is changed, the maximum value of SEQUENCE will also be updated. However, changes to reduce
the current sequence value are not allowed.

postgres=> CREATE TABLE instead1(c1 NUMERIC, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE VIEW insteadv1 AS SELECT c1, c2 FROM instead1 ;
CREATE VIEW
postgres=> CREATE OR REPLACE FUNCTION view_insert_row1() RETURNS trigger AS
 $$
 BEGIN
    INSERT INTO instead1 VALUES (new.c1, new.c2);
    RETURN new;
 END;
 $$
 LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=> CREATE TRIGGER insteadv1_insert
 INSTEAD OF INSERT ON insteadv1 FOR EACH ROW
 EXECUTE PROCEDURE view_insert_row1() ;
CREATE TRIGGER
postgres=# COPY insteadv1 FROM '/home/postgres/instead.csv' ;
COPY 2

postgres=>  CREATE INDEX idx1_brin1 ON brin1 USING brin (c1) WITH
(autosummarize) ;
CREATE INDEX
postgres=> \d brin1
                      Table "public.brin1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
Indexes:
    "idx1_brin1" brin (c1) WITH (autosummarize='true')

postgres=# CREATE USER user1 UNENCRYPTED PASSWORD 'user1' ;
ERROR:  UNENCRYPTED PASSWORD is no longer supported
LINE 1: CREATE USER user1 UNENCRYPTED PASSWORD 'user1' ;
                          ^
HINT:  Remove UNENCRYPTED to store the password in encrypted form instead.

postgres=> SELECT '{"a":1 , "b":2, "c":3}'::jsonb - '{a,c}'::text[] ;
 ?column?
----------
 {"b": 2}
(1 row)

postgres=# SELECT pg_current_logfile() ;
           pg_current_logfile
-----------------------------------------
log/postgresql-2017-05-20_092939.log
(1 row)

postgres=> SELECT xmltable.*
postgres->   FROM xmldata,
postgres->        XMLTABLE('//ROWS/ROW'
postgres(>                 PASSING data
postgres(>                 COLUMNS id int PATH '@id',
postgres(>                         ordinality FOR ORDINALITY,
postgres(>                         "COUNTRY_NAME" text,
postgres(>                         country_id text PATH 'COUNTRY_ID',
postgres(>                         size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
postgres(>                         size_other text PATH
postgres(>                  'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
postgres(>                   premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;
 id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name
----+------------+--------------+------------+------------+--------------+---------------
  1 |          1 | Australia    | AU         |            |              | not specified
  5 |          2 | Thailand     | TH         |            |              | Prayuth Chan
  6 |          3 | Singapore    | SG         |        697 |              | not specified
(3 rows)

postgres=> \dfS regexp_match
                              List of functions
   Schema   |     Name     | Result data type | Argument data types |  Type
------------+--------------+------------------+---------------------+--------
 pg_catalog | regexp_match | text[]           | text, text          | normal
 pg_catalog | regexp_match | text[]           | text, text, text    | normal
(2 rows)

postgres=# SELECT * FROM pg_ls_logdir() ;
               name               | size |      modification
----------------------------------+------+------------------------
postgresql-2017-05-20_092939.log |  5220 | 2017-05-20 21:44:21+09
(1 row)
postgres=# SELECT * FROM pg_ls_waldir() ;
           name           |   size   |      modification
--------------------------+----------+------------------------
00000001000000000000002E | 16777216 | 2017-05-19 22:55:33+09
(1 row)

postgres=> BEGIN ;
BEGIN
postgres=> SELECT txid_current() ;
 txid_current
--------------
          578
(1 row)
postgres=> SELECT txid_status(578) ;
 txid_status
-------------
 in progress
(1 row)
postgres=> COMMIT ;
COMMIT
postgres=> SELECT txid_status(578) ;
 txid_status
-------------
 committed
(1 row)
postgres=> SELECT txid_status(1000) ;
ERROR:  transaction ID 1000 is in the future

postgres=> SELECT to_date('2017-04-40', 'YYYY-MM-DD') ;
  to_date
------------
 2017-05-10
(1 row)

postgres=> SELECT to_date('2017-04-40', 'YYYY-MM-DD') ;
ERROR:  date/time field value out of range: "2017-04-40"

postgres=> SELECT make_date(-2000, 4, 30) ;
ERROR:  date field value out of range: -2000-04-30

postgres=> SELECT make_date(-2000, 4, 30) ;
   make_date
---------------
 2000-04-30 BC
(1 row)

# plan.execute
plan = plpy.prepare("SELECT val FROM data1 WHERE key=$1", [ "NUMERIC" ])
result = plan.execute(key)
# plan.cursor
plan = plpy.prepare("SELECT val FROM data1 WHERE key=$1", [ "NUMERIC" ])
rows = plan.cursor([2])

CREATE FUNCTION transfer_funds2() RETURNS void AS $$
if [catch {
subtransaction {
        spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
        spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
   }
} errormsg] {
set result [format "error transferring funds: %s" $errormsg]
} else {
      set result "funds transferred correctly"
}
    set plan  [spi_prepare "INSERT INTO operations (result) VALUES ($1)"]
    spi_execp $plan, [list $result]
$$ LANGUAGE pltclu;

Pltcl.start_proc and pltclu.start_proc which are GUC which specifies initialization procedure name
have been added.