XhstormR


On a dark desert highway Cool wind in my hair


自学 PostgreSQL

Updated on 2017-02-19

https://www.postgresql.org/

https://jdbc.postgresql.org/download.html

https://www.postgresql.org/docs/current/index.html

https://github.com/pgjdbc/pgjdbc

https://github.com/postgis/postgis

https://github.com/postgres/postgres

https://jcenter.bintray.com/org/postgresql/postgresql/

https://yum.postgresql.org/repopackages.php

Step 1 | Step 2 | Step 3

Concept

  • 结构化查询语言(SQL):Structured Query Language
    • 数据定义语言(DDL):Data Definition Language
      • 用于建立(CREATE)、修改(ALTER)、删除(DROP)数据库对象。
    • 数据操纵语言(DML):Data Manipulation Language
      • 用于改变(INSERT、UPDATE、DELETE)数据库数据。
    • 数据查询语言(DQL):Data Query Language
      • 用于查询(SELECT)所需要的数据。
    • 数据控制语言(DCL):Data Control Language
      • 用于权限的授予(GRANT)和收回(REVOKE),创建用户(CREATE USER)。
    • 事务控制语言(TCL):Transaction Control Language
      • 用于维护数据一致性的语句,包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)。
  • 增删改查(CRUD): SQL;HTTP
    • 加(Create):INSERT;POST(非幂等)
    • 询(Retrieve):SELECT;GET
    • Update):UPDATE;PATCH(更新资源:非幂等),PUT(替换资源:幂等)
    • 除(Delete):DELETE;DELETE
  • 事务(Transaction):逻辑上的 一组操作,这组操作只能一起成功或者一起失败。
    • 数据库中 保证事务可靠 的机制(ACID)。
      • 原子性(Atomicity):对于数据修改,要么全都执行,要么全都不执行。
      • 一致性(Consistency):所有的数据都保持一致状态。
      • 隔离性(Isolation):与其它并发事务所作的修改隔离。
      • 持久性(Durability):对于系统的影响是永久性的。
  • 数据访问对象(DAO):Data Access Object
    • 用于封装所有对数据库的访问,使数据访问逻辑和业务逻辑分开。
    • 数据传递对象
      • 值对象(Value Object)
      • 实体对象(Entity)
  • 对象关系映射(ORM):Object Relation Mapping * 中的对象属性(Java)<— 映射(ORM) —>中的记录字段(Database)

Initial

initdb.exe -A scram-sha-256 -E UTF8 --no-locale --lc-messages="Chinese (Simplified)_China.936" -U 123 -W -D D:\12345
pg_ctl.exe -l D:\log.txt -D D:\12345 start
pg_ctl.exe -l D:\log.txt -D D:\12345 status
pg_ctl.exe -l D:\log.txt -D D:\12345 stop
psql.exe -e -E -h 127.0.0.1 -p 5432 -U 123 -W -d postgres

compile("org.postgresql:postgresql:+")

导出:pg_dump.exe -h 127.0.0.1 -p 5432 -U 123 -W -d postgres -f data.sql
导入:psql.exe -h 127.0.0.1 -p 5432 -U 123 -W -d postgres -f data.sql

Operate

\c     切换数据库
\l     列出数据库
\d     列出表、序列、视图
\dt     列出表
\du     列出角色
\dn     列出模式
\df     列出函数
\dx     列出扩展

\g     执行查询缓存区
\p     显示查询缓存区
\r     重置查询缓存区
\e     使用外部编辑器编辑查询缓存区
\w     将当前查询缓存区写至文件
\o     将所有查询结果写至文件
\i     将文件写至查询缓存区

\x     垂直显示查询结果(\x\g\x\t     只显示查询结果(无页眉和页脚)
\H     切换为 HTML 输出模式

\q     退出 psql
\h     SQL 语法说明
\!     执行外部命令
\set PROMPT1 123     设置提示符(https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING)
\pset pager off     关闭分页
\timing     计时
\conninfo     显示连接信息
\password     修改密码
\encoding     修改客户端编码

show all;     显示所有系统信息
show config_file;     显示系统配置文件
show server_version;     显示系统版本
show server_encoding;     显示服务端编码
show client_encoding;     显示客户端编码

select version();     显示系统版本
select now();     显示当前日期+时间
select current_timestamp;     显示当前日期+时间
select current_date;     显示当前日期
select current_time;     显示当前时间
select current_user;     显示当前用户
select current_database();     显示当前数据库
select pg_postmaster_start_time();     显示系统启动日期

{}     必选项
[]     可选项
|     选择分隔符

''     表示字符串
""     表示标识符(用于区分大小写和关键字;不常用,因为一般情况下标识符都为小写,并且不会与关键字重名,空格用 '_' 代替)
     建议双引号要么都带,要么都不带。

Table

Create

DROP TABLE a;

-------------------------------------------------------

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC
);

-------------------------------------------------------


CREATE TABLE a (
  id    SERIAL,     
  name  TEXT,
  price NUMERIC DEFAULT 9.99     
);

-------------------------------------------------------





CREATE TABLE a (
  id    INTEGER,
  name  TEXT NOT NULL,     
  price NUMERIC,
  CHECK (price > 0),     "a_price_check"
  CONSTRAINT abc CHECK (price > 0)     "abc"
);

----


CREATE TABLE a (
  id    INTEGER UNIQUE,     "a_id_key"
  name  TEXT,
  price NUMERIC
);

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC,
  UNIQUE (id)     "a_id_key"
);

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC,
  CONSTRAINT abc UNIQUE (id)     "abc"
);

----
 =  + 

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC,
  UNIQUE (id, name)     "a_id_name_key"
);

-------------------------------------------------------
 = =  =  +  = 
PRIMARY KEY = UNIQUE NOT NULL

CREATE TABLE a (
  id    INTEGER PRIMARY KEY,     "a_pkey"
  name  TEXT,
  price NUMERIC
);

CREATE TABLE a (
  id    INTEGER CONSTRAINT abc PRIMARY KEY,     "abc"
  name  TEXT,
  price NUMERIC
);

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC,
  PRIMARY KEY (id)     "a_pkey"
);

----
 =  + 

CREATE TABLE a (
  id    INTEGER,
  name  TEXT,
  price NUMERIC,
  PRIMARY KEY (id, name)     "a_pkey"
);

-------------------------------------------------------
 = =  = 

CREATE TABLE a (     
  id    INTEGER PRIMARY KEY,     
  name  TEXT,
  price NUMERIC
);

CREATE TABLE b (     
  id    INTEGER PRIMARY KEY,     
  a_no  INTEGER REFERENCES a (id),     
  count INTEGER
);

CREATE TABLE b (
  id    INTEGER PRIMARY KEY,
  a_no  INTEGER REFERENCES a,     
  count INTEGER
);

CREATE TABLE b (
  id    INTEGER PRIMARY KEY,
  a_no  INTEGER,
  count INTEGER,
  FOREIGN KEY (a_no) REFERENCES a     
);

CREATE TABLE b (     
  id    INTEGER,
  a_no  INTEGER,
  count INTEGER,
  PRIMARY KEY (id),     
  FOREIGN KEY (a_no) REFERENCES a     
);

-------------------------------------------------------



ON DELETE
ON UPDATE


NO ACTION     
RESTRICT     
CASCADE      or 
SET DEFAULT     
SET NULL      NULL

CREATE TABLE b (     
  id    INTEGER,
  a_no  INTEGER,
  count INTEGER,
  PRIMARY KEY (id),     
  FOREIGN KEY (a_no) REFERENCES a ON UPDATE CASCADE     
);

Alter


-------------------------------------------------------
ALTER TABLE a
  ADD COLUMN description TEXT;      NULL

ALTER TABLE a
  ADD COLUMN description TEXT DEFAULT '未描述' CHECK (description != '');     使 CREATE TABLE 


-------------------------------------------------------
ALTER TABLE a DROP COLUMN description;

     
-------------------------------------------------------
ALTER TABLE a ADD CHECK (name != '');     
ALTER TABLE a ADD UNIQUE (name);     
ALTER TABLE a ADD FOREIGN KEY (name) REFERENCES b;     
ALTER TABLE a ALTER COLUMN name SET NOT NULL;     


-------------------------------------------------------
ALTER TABLE a DROP CONSTRAINT "a_name_check";     
ALTER TABLE a ALTER COLUMN name DROP NOT NULL;     

     
-------------------------------------------------------
ALTER TABLE a ALTER COLUMN description SET DEFAULT '未描述';     
ALTER TABLE a ALTER COLUMN description DROP DEFAULT;      NULL

     使 USING 
-------------------------------------------------------
ALTER TABLE a ALTER COLUMN description TYPE VARCHAR;


-------------------------------------------------------
ALTER TABLE a RENAME COLUMN description TO characterization;


-------------------------------------------------------
ALTER TABLE a RENAME TO b;

Schema

Cluster pg_databasepg_grouppg_shadow

Schema



 ->  ->  -> ...

-------------------------------------------------------

CREATE SCHEMA a;     

-------------------------------------------------------

DROP SCHEMA a;     

DROP SCHEMA a CASCADE;     

-------------------------------------------------------
PostgreSQL 访 public 

CREATE TABLE a ();

CREATE TABLE public.a ();

-------------------------------------------------------


SHOW search_path;     

SET search_path TO a,public;     

Inherit

CREATE TABLE a (     
  a_id INTEGER
);

CREATE TABLE b (     
  b_id INTEGER
) INHERITS (a);      a  a 

----

postgres=# \d+ a     
                  "public.a"
  |     |  |   |  | 
 a_id | integer |        | plain |          |
: b

postgres=# \d+ b     
                  "public.b"
  |     |  |   |  | 
 a_id | integer |        | plain |          |
 b_id | integer |        | plain |          |
: a

-------------------------------------------------------

INSERT INTO a VALUES (1);     
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO b VALUES (1, 101);     

SELECT * FROM a;     
 a_id
------
    1
    2
    3
    1
SELECT * FROM ONLY a;     SELECTUPDATEDELETE  ONLY ;
 a_id
------
    1
    2
    3
SELECT * FROM b;     
 a_id | b_id
------+------
    1 |  101

-------------------------------------------------------

TRUNCATE TABLE a;     使 TRUNCATE 
SELECT * FROM a;     
 a_id
------

-------------------------------------------------------


SELECT
  a.*,
  a.TABLEOID     tableoid 
FROM a;
 a_id | tableoid
------+----------
    1 |    17126
    2 |    17126
    3 |    17126
    1 |    17129

SELECT
  a.*,
  p.relname
FROM a, pg_class p
WHERE a.TABLEOID = p.OID;      tableoid  pg_class 
 a_id | relname
------+---------
    1 | a
    2 | a
    3 | a
    1 | b

----

SELECT
  a.*,
  a.TABLEOID :: REGCLASS
FROM a;
 a_id | tableoid
------+----------
    1 | a
    2 | a
    3 | a
    1 | b

-------------------------------------------------------

                 


访访访访使 ONLY 

Partition





CREATE TABLE a (
  id     INTEGER,
  gender TEXT,
  name   TEXT
);

CREATE TABLE b1 (
  PRIMARY KEY (id),
  CHECK (gender = '')
)
  INHERITS (a);

CREATE TABLE b2 (
  PRIMARY KEY (id),
  CHECK (gender = '')
)
  INHERITS (a);

Function

SQL(查询语言函数)


 SQL ''$$$$
INOUTINOUT+VARIADIC

CREATE TABLE a (     
  name   TEXT,
  age    INTEGER,
  salary NUMERIC
);

INSERT INTO a VALUES ('小张', 25, 4999.9);     
INSERT INTO a VALUES ('小陈', 23, 3999.9);

-------------------------------------------------------
 Void SELECTINSERTUPDATE RETURNING  DELETE

CREATE OR REPLACE FUNCTION a(IN INTEGER, OUT VOID) AS $$      INTEGER Void
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION a(INTEGER) RETURNS VOID AS $$     INOUT
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION a(INTEGER) RETURNS VOID AS ''     使 ''  $$$$ 
LANGUAGE SQL;

DROP FUNCTION a( INTEGER );     

SELECT a(1);      VoidNULL

-------------------------------------------------------


CREATE OR REPLACE FUNCTION a(OUT INTEGER) AS $$      INTEGER
SELECT 1;      1
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT a();

----

CREATE OR REPLACE FUNCTION a(OUT VOID) AS $$      NULL
DROP TABLE a CASCADE;      a
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT a();

----

CREATE OR REPLACE FUNCTION a(INTEGER, INTEGER, OUT INTEGER) AS $$
SELECT $1 + $2;      $n 
$$ LANGUAGE SQL;

DROP FUNCTION a( INTEGER, INTEGER );

SELECT a(1, 2);      3

-------------------------------------------------------

CREATE OR REPLACE FUNCTION a(a a, OUT INTEGER) AS $$
SELECT (a.salary * 2) :: INTEGER;     NUMERIC  INTEGER
$$ LANGUAGE SQL;

DROP FUNCTION a( a );

SELECT name, a(a) dream FROM a;
 name | dream
------+-------
  | 10000
  |  8000
SELECT name, a(ROW (name, age, salary * 1.1)) dream FROM a;     ROW 
 name | dream
------+-------
  | 11000
  |  8800

-------------------------------------------------------


CREATE OR REPLACE FUNCTION a(OUT a) AS $$      a 
SELECT ROW ('小红', 21, 3499.9) :: a;     Record  a
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT ROW ('小红', 21, 3499.9) :: a;

SELECT a();
       row
------------------
 (,21,3499.9)

SELECT (a()).name;
 name
------
 
SELECT * FROM a();
 name | age | salary
------+-----+--------
  |  21 | 3499.9

----

CREATE OR REPLACE FUNCTION a(x INTEGER, y INTEGER, OUT sum INTEGER, OUT multiply INTEGER) AS $$      Record 
SELECT x + y, x * y;     
$$ LANGUAGE SQL;

DROP FUNCTION a( INTEGER, INTEGER );

SELECT * FROM a(5, 10);

SELECT (a(5, 10)).*;
 sum | multiply
-----+----------
  15 |       50

-------------------------------------------------------
 FROM SETOFRETURNS TABLE


CREATE OR REPLACE FUNCTION a() RETURNS SETOF a AS $$      a 
SELECT * FROM a;
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT * FROM a();
 name | age | salary
------+-----+--------
  |  25 | 4999.9
  |  23 | 3999.9

----

CREATE OR REPLACE FUNCTION a(OUT name TEXT, OUT salary NUMERIC) RETURNS SETOF RECORD AS $$      Record 
SELECT name, salary FROM a;
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT * FROM a();
 name | salary
------+--------
  | 4999.9
  | 3999.9



CREATE OR REPLACE FUNCTION a() RETURNS TABLE(name TEXT, salary NUMERIC) AS $$     RETURNS TABLE  RETURNS SETOF  Record 
SELECT name, salary FROM a;
$$ LANGUAGE SQL;

DROP FUNCTION a();

SELECT * FROM a();
 name | salary
------+--------
  | 4999.9
  | 3999.9

-------------------------------------------------------
ANYELEMENTANYARRAYANYNONARRAYANYENUM

CREATE OR REPLACE FUNCTION a(ANYELEMENT, ANYELEMENT, OUT ANYARRAY) AS $$
SELECT ARRAY [$1, $2];
$$ LANGUAGE SQL;

DROP FUNCTION a( ANYELEMENT, ANYELEMENT );

SELECT a(1, 2), a('A' :: TEXT, 'B');     
   a   |   a
-------+-------
 {1,2} | {A,B}

----

CREATE OR REPLACE FUNCTION a(ANYELEMENT, ANYELEMENT, OUT BOOLEAN) AS $$
SELECT $1 > $2;
$$ LANGUAGE SQL;

DROP FUNCTION a( ANYELEMENT, ANYELEMENT );

SELECT a(2, 1), a(1, 2), a('A' :: TEXT, 'B');
 a | a | a
---+---+---
 t | f | f

----

CREATE OR REPLACE FUNCTION a(i ANYELEMENT, OUT o1 ANYELEMENT, OUT o2 ANYARRAY) AS $$
SELECT
  i,
  ARRAY [i, i];
$$ LANGUAGE SQL;

DROP FUNCTION a( ANYELEMENT );

SELECT * FROM a(2);
 o1 |  o2
----+-------
  2 | {2,2}

-------------------------------------------------------
DEFAULT

CREATE OR REPLACE FUNCTION a(x INTEGER DEFAULT 1, y INTEGER DEFAULT 10, z INTEGER DEFAULT 100, OUT INTEGER) AS $$
SELECT x + y + z;
$$ LANGUAGE SQL;

DROP FUNCTION a( INTEGER, INTEGER, INTEGER );

SELECT a();
  a
-----
 111
SELECT a(0);
  a
-----
 110
SELECT a(0, 0);
  a
-----
 100
SELECT a(0, 0, 0);
 a
---
 0
SELECT a(z := 0);     
 a
----
 11

-------------------------------------------------------
VARIADIC

CREATE OR REPLACE FUNCTION a(VARIADIC TEXT [], OUT INTEGER) AS $$
SELECT array_length($1, 1);     
$$ LANGUAGE SQL;

DROP FUNCTION a( TEXT [] );

SELECT a('a', 'b', 'c');
 a
---
 3
SELECT a(VARIADIC ARRAY ['a', 'b', 'c']);
 a
---
 3
SELECT a(VARIADIC ARRAY []:: TEXT []);      NULL

----

SELECT generate_subscripts(ARRAY ['a', 'b', 'c'], 1);     
 generate_subscripts
---------------------
                   1
                   2
                   3

SELECT * FROM generate_subscripts(ARRAY ['a', 'b', 'c'], 1) abc(def);
 def
-----
   1
   2
   3

CREATE OR REPLACE FUNCTION a(VARIADIC ANYARRAY, OUT ANYELEMENT) AS $$;
SELECT min($1 [def]) FROM generate_subscripts($1, 1) abc(def);     
$$ LANGUAGE SQL;
Note
min($1 [def])  def  min() 

DROP FUNCTION a( ANYARRAY );

SELECT a(10, -1, 5, 4, 6);
 a
----
 -1
SELECT a('a' :: TEXT, 'b', 'c');
 a
---
 a

PL/pgSQL(程序语言函数)


[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

-------------------------------------------------------

CREATE OR REPLACE FUNCTION a() RETURNS INTEGER AS $$
BEGIN
  RAISE NOTICE '% %', 'ABC', 123;
  RETURN 1;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT a();
:  ABC 123
 a
---
 1

-------------------------------------------------------


CREATE OR REPLACE FUNCTION a() RETURNS INTEGER AS $$
  << abc >>
  DECLARE
  a INTEGER := 30;
BEGIN
  RAISE NOTICE 'Out: a = %', a;     30
  a := 50;

    << def >>     
    DECLARE
    a INTEGER := 100;
  BEGIN
    RAISE NOTICE 'In: a = %', a;     100
    RAISE NOTICE 'In: def.a = %', def.a;     100
    RAISE NOTICE 'In: abc.a = %', abc.a;     50
  END;     

  RAISE NOTICE 'Out: a = %', a;     50
  RETURN a;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT a();
:  Out: a = 30
:  In: a = 100
:  In: def.a = 100
:  In: abc.a = 50
:  Out: a = 50
 a
----
 50

-------------------------------------------------------
PL/pgSQL  DECLARE 
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

CREATE OR REPLACE FUNCTION a() RETURNS VOID AS $$
DECLARE
  a          INTEGER;     NULL
  b          INTEGER DEFAULT 1;     
  c          INTEGER NOT NULL = 2;      NULL
  d          INTEGER = 3;     
  e CONSTANT INTEGER = 4;     
BEGIN
  RAISE NOTICE '% % % % %', a, b, c, d, e;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT a();
:  <NULL> 1 2 3 4
 a
---

----

CREATE OR REPLACE FUNCTION a() RETURNS TEXT AS $$
DECLARE
  a TIMESTAMP DEFAULT now();     
BEGIN
  RETURN a;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT a();
             a
----------------------------
 2017-03-02 15:15:27.452181

-------------------------------------------------------



CREATE OR REPLACE FUNCTION a(INTEGER, INTEGER, OUT sum INTEGER, OUT multiply INTEGER) AS $$
BEGIN
  sum = $1 + $2;
  multiply = $1 * $2;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a( INTEGER, INTEGER );

SELECT * FROM a(5, 10);

SELECT (a(5, 10)).*;
 sum | multiply
-----+----------
  15 |       50


CREATE OR REPLACE FUNCTION a() RETURNS TABLE(name TEXT, salary NUMERIC) AS $$
BEGIN
  RETURN QUERY SELECT abc.name, abc.salary FROM a abc;     
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT * FROM a();
 name | salary
------+--------
  | 4999.9
  | 3999.9


CREATE OR REPLACE FUNCTION a(ANYELEMENT, ANYELEMENT, ANYELEMENT, OUT o ANYELEMENT) AS $$
BEGIN
  o = $1 + $2 + $3;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a( ANYELEMENT, ANYELEMENT, ANYELEMENT );

SELECT a(1, 2, 3);
 a
---
 6

-------------------------------------------------------
%TYPE%ROWTYPE

CREATE OR REPLACE FUNCTION a(a.age%TYPE, OUT s TEXT) AS $$
DECLARE
  o a%ROWTYPE;
BEGIN
  SELECT * INTO o FROM a WHERE age = $1;      o 
  s = o.name || ' ' || o.age || ' ' || o.salary;
END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a( INTEGER );

SELECT a(25);
       a
----------------
  25 4999.9


name RECORD;

-------------------------------------------------------
PL/pgSQL  'IF expression THEN ...'  SQL 
 'SELECT expression'' 来计算表达式的返回值。

赋值:variable { := | = } expression;
若数据类型不匹配,将强制转换;若转换失败,将以文本方式转换,否则发生异常。

a = b * 0.06;
my_record.user_id = 20;

-------------------------------------------------------
没有返回值的命令:PERFORM query;
执行命令并忽略其返回值,与 SELECT 写法一致,只是将 SELECT 替换为 PERFORM。

PERFORM create_mv('cs_session_page_requests_mv', my_query);

-------------------------------------------------------
返回一行结果的命令:INTO target
除了 INTO 子句,SQL 语句的其他部分的语法不变。

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

无 STRICT:target 为该查询返回的第一个行,无结果则为 NULL。
----
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN     没有行(try-catch)
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

有 STRICT:该查询必须刚好返回一个行,否则发生异常;成功执行带 STRICT 的命令总是会将 FOUND 置为真。
----
BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN     没有行(try-catch)
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN     多于一行(try-catch)
            RAISE EXCEPTION 'employee % not unique', myname;
END;

CREATE OR REPLACE FUNCTION a(INTEGER, OUT r a) AS $$
BEGIN
  SELECT * INTO STRICT r FROM a WHERE age = $1;
  EXCEPTION
  WHEN NO_DATA_FOUND
    THEN
      RAISE EXCEPTION 'age % not found', $1;
  WHEN TOO_MANY_ROWS
    THEN
      RAISE EXCEPTION 'age % not unique', $1;
END
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a( INTEGER );

SELECT * FROM a(25);
 name | age | salary
------+-----+--------
 小张 |  25 | 4999.9
SELECT * FROM a(99);
错误:  age 99 not found
背景:  在RAISE的第10行的PL/pgSQL函数a(integer)

-------------------------------------------------------
占位语句(什么也不做):NULL;
能够指示 if/then/else 链中故意留出的空分支。

CREATE OR REPLACE FUNCTION a() RETURNS VOID AS $$
DECLARE
  x INTEGER = 1;
  y INTEGER;
BEGIN
  y = x / 0;

  EXCEPTION
  WHEN division_by_zero
    THEN     忽略异常
  等同于
  EXCEPTION
  WHEN division_by_zero
    THEN
      NULL;     忽略异常(更直观)

END;
$$ LANGUAGE PLPGSQL;

DROP FUNCTION a();

SELECT a();

EXPLAIN ANALYZE SELECT * FROM customer; --显示语句的执行计划
ALTER TABLE account DISABLE TRIGGER ALL; --禁用外键约束
ALTER TABLE account ENABLE  TRIGGER ALL; --启用外键约束

Tool

TOP