Skip to main content

PostgreSQL SQL 问题

JSON 数组转行#

-- 转换为单行 JSON 对象select * from json_array_elements('[{"a":1},{"a":2}]');
-- 同时提取列select * from json_to_recordset('[{"a":1,"b":"first"},{"a":2,"b":"second"}]') as (a int, b text);

JSON 对象遍历#

-- (key,value)select * from json_each_text('{"a":1,"b":2}') as d;

静态数据行#

SELECT *FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);

分组聚合#

  • cube
  • GROUPING SETS, CUBE, ROLLUP
  • rollup(a,b,c) => grouping sets((a,b,c),(a,b),(a),())
  • cube((a),(b),(c)) grouping sets((a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),())
GROUP BY a, b, c-- 对等

ROLLUP ( a, b , c)-- 对等GROUPING SETS (    ( a, b, c ),    ( a, b    ),    ( a       ),    (         ))
CUBE ( a, b, c )-- 对等GROUPING SETS (    ( a, b, c ),    ( a, b    ),    ( a,    c ),    ( a       ),    (    b, c ),    (    b    ),    (       c ),    (         ))
CUBE ( (a, b), (c, d) )-- 对等GROUPING SETS (    ( a, b, c, d ),    ( a, b       ),    (       c, d ),    (            ))

function vs procedure#

  • function
    • 不可以操作事物
    • 使用 SELECT 调用
  • procedure
    • 没有返回值
    • 有 INOUT 参数
    • 可以 commit 和 rollback
    • 使用 CALL 调用
    • 不可以嵌套到其他 DDL - SELECT,INSERT,UPDATE,DELETE

函数返回影响行#

get diagnostics cnt = row_count;return cnt;