彥霖 實驗筆記

-- 先建立外部模組
CREATE EXTENSION pgcrypto;
-- 再建立 test_user 表格

雖然我標題寫 Json、Jsonb 操作，但實際上我這裡只會用 Jsonb 來示範
會用 Jsonb 實際上就會用 Json ，因為 Jsonb 效能比較好 (資料無排序)，但如果你要有排序功能，可以用傳統 Json 來操作
前置作業重點：
插入 Jsonb 無法用 INSERT INTO ，因為他找不到 資料 插....，所以要先用 INSERT INTO 先插入一個 空 資料，這樣未來 UPDATE 才找的到洞插入...
更多資源可以參考 PostgreSQL 官方的 Json 文件：http://www.postgresql.org/docs/9.5/static/function...

範例：
CREATE OR REPLACE FUNCTION x() RETURNS TABLE(a INT, b TEXT, c TEXT) AS
$$
    DECLARE data TEXT := 'Loli';
    BEGIN
        RETURN QUERY SELECT 5, 'Hello'::TEXT, data;
    END;
$$
LANGUAGE plpgsql;
執行： SELECT * FROM x();
結果：
 

-- 判斷表是否已經有資料了? 沒有的話就做初始化動作，也就是插入 {} 大括號，以初始化 location-Jsonb
IF NOT EXISTS (SELECT * FROM location) THEN
    INSERT INTO location VALUES ('{}');
END IF;

查看擴充套件
select * from pg_extension;
列出可安裝清單，uuid-ossp 會出現在裡面
select * from pg_available_extensions;
安裝
CREATE EXTENSION "uuid-ossp";
查看 UUID
SELECT uuid_generate_v1();

官方文件：陣列 http://www.postgresql.org/docs/9.5/static/functions-array.html
建立 run () 涵式：
整數陣列：
CREATE OR REPLACE FUNCTION run(data int[]) RETURNS SETOF int AS
    $BODY$
        DECLARE i int;
    BEGIN
        FOR i IN 1 .. array_upper(data, 1) LOOP
            RETURN NEXT i;
        END LOOP;
    END
    $BODY$
LANGUAGE 'plpgsql';
查詢：
SELECT run('{1,2,3}');
輸出結果：
 run

1. 用現在時間寫入 Json 
SELECT jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now()));
2. Json 時間比大小：
假設我們有以下資料

這是 PostgreSQL 9.5 版以上才有的語法
詳細可參考這裡：http://www.postgresql.org/docs/9.5/static/functions-json.html
1. 首先我們先建立一個 Json 表格
CREATE TABLE json (data JSONB);
2. 插入 Json 資料
INSERT INTO json VALUES ('{"A":"B"}');
3. 查看我們剛剛的資料
SELECT * FROM json;
  data
------------
{"A": "B"}
4. 更新資料 (在舊 Json 中接續插入新的 Json) 
UPDATE json SET data = jsonb_set(data, '{X, Loli}', '"Love"');
5. 察看結果 (舊資料中出現新的Json資料了)：
SELECT * FROM json;
              data
-----------------------------------
{"A": "B", "X": {"Loli": "Love"}}
6. 加入陣列
UPDATE json SET data = jsonb_set(data, '{X}', '[]');
SELECT * FROM json;
      data
---------------------
{"A": "B", "X": []}
7. 插入陣列新值：
UPDATE json SET data = jsonb_set(data, '{X, *}', '0');
UPDATE json SET data = jsonb_set(data, '{X, *}', '1');
UPDATE json SET data = jsonb_set(data, '{X, *}', '100');
SELECT * FROM json;
      data
---------------------
{"A": "B", "X": [0, 1, 100]}
8. 刪掉陣列中其中一個值：
UPDATE json SET data = data #- '{X, 2}';
      data
---------------------
{"A": "B", "X": [0, 1, 100]}

1. 先建立一個 users 表格：
CREATE TABLE users (
    id    serial    PRIMARY    KEY,
    username    TEXT    NOT    NULL,
    passwd    TEXT
);
2. 然後連續插入三次
INSERT INTO users (username, passwd) VALUES ('Loli', 'Love');
INSERT INTO users (username, passwd) VALUES ('Loli', 'Love');
INSERT INTO users (username, passwd) VALUES ('Loli', 'Love');
3. 然後搜尋
SELECT * FROM users;
4. 輸出結果 (會發現 id 自動有流水號)：

crypt 就算插入相同的文字，也會顯示不同密碼
CREATE EXTENSION pgcrypto;
AES 加密：
create table demo(pw bytea);
insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
AES 解密：
select encode( decrypt(pw,'key','aes'), 'escape') FROM demo;
crypt() 加密：
create table demo2(pw text);
insert into demo2 (pw) values ( crypt('data',gen_salt('md5')) );   -- md5 也可改成 bf