PostgreSQL Tips: 動態SQL

2021-02-21 PostgreSQL
前言

前幾天 Postgres 群裡有人問如何在 Postgres 裡實現動態列的行轉列，於是我整理了PostgreSQL 實現動態行轉列的方法匯總

然後被問到如何直接通過一個函數返回結果，而不是返回一個待執行的 SQL 語句，於是我參考下面的教程，實現了一個函數返回 json，先把代碼附上，

-- PL/pgSQL code to create pivot tables with automatic column names

-- prerequisite: install the tablefunc module
create extension if not exists tablefunc;

-- tablename: name of source table you want to pivot
-- rowc: the name of the column in source table you want to be the rows
-- colc: the name of the column in source table you want to be the columns
-- cellc: an aggregate expression determining how the cell values will be created
-- celldatatype: desired data type for the cells
create or replace function pivot (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar)
returns json language plpgsql as $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
    stmt TEXT;
    result json;

begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'select * from crosstab (
 ''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
 ''select distinct '||colc||' from '||tablename||' order by 1''
 )
 as newtable (
 '||rowc||' varchar,'||columnlist||'
 )';

stmt = E' select array_to_json(array_agg(row_to_json(t)))  from ('|| dynsql2 ||') t ';

execute stmt into result;
return result;
end
$$

這裡就是把 pivotcode 生成的 SQL 執行，並把結果存到 json 裡返回
stmt = E' select array_to_json(array_agg(row_to_json(t)))  from ('|| dynsql2 ||') t ';

execute stmt into result;
正文
在本 PostgreSQL 教程中，我們將仔細研究動態 SQL 的概念，以及如何通過允許高效地查詢數據來簡化資料庫程式設計師的工作。
在查詢中，動態 SQL 用於減少重複性任務。例如，可以使用動態 SQL 每天為特定表創建表分區，在所有外鍵上添加缺失的索引，或在不產生重大編碼影響的情況下為特定表添加數據審核功能。動態 SQL 的另一個重要用途是克服 PL/pgSQL 緩存的副作用，因為不緩存使用 EXECUTE 語句執行的查詢。
動態 SQL 是通過 EXECUTE 語句實現的。該 EXECUTE 語句接受一個字符串，並簡單地計算它。執行語句的摘要如下：
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ...]];
在動態 SQL 中執行 DDL 語句
在某些情況下，需要在資料庫對象級別執行操作，例如表，索引，列，角色等。例如，資料庫開發人員希望清理並分析特定的架構對象，這是部署後的常見任務，目的是更新統計信息。例如，要分析 schema public 下的表 ，可以編寫以下腳本：
DO $$

    DECLARE
    table_name text;

    BEGIN

    FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname ='public' LOOP

    RAISE NOTICE 'Analyzing %', table_name;

    EXECUTE 'ANALYZE ' || table_name;

    END LOOP;

    END;

    $$;
在動態 SQL 中執行 DML 語句
某些應用程式可能以交互方式與數據交互。例如，一個人可能每月生成一次計費數據。此外，某些應用程式會根據用戶定義的不同標準過濾數據。在這種情況下，動態 SQL 非常方便。例如，在汽車門戶應用程式中，需要使用搜索功能才能使用動態謂詞獲取帳戶，如下所示：
CREATE OR REPLACE FUNCTION get_account (predicate TEXT)
RETURNS SETOF account AS$$

BEGIN

RETURN QUERY EXECUTE 'SELECT * FROM account WHERE ' || predicate;

END;

$$ LANGUAGE plpgsql;
要測試以前的功能：
car_portal=> SELECT * FROM get_account ('true') limit 1;
account_id | first_name | last_name | email | password

--+--+-+--+----
1 | James | Butt | jbutt@gmail.com |1b9ef408e82e38346e6ebebf2dcc5ece
(1 row)

car_portal=> SELECT * FROM get_account(E'first_name='James'');

account_id | first_name | last_name | email | password
--+--+-+--+----
1 | James | Butt | jbutt@gmail.com |1b9ef408e82e38346e6ebebf2dcc5ece

(1 row)
動態 SQL 及其緩存效果
如前所述，PL / pgSQL 緩存執行計劃。如果預期生成的計劃是靜態的，則這非常好。例如，由於選擇性，下面的語句應使用索引掃描。在這種情況下，緩存計劃可以節省一些時間，從而 提高性能：
SELECT * FROM account WHERE account_id =<INT>
但是，在其他情況下，情況並非如此。例如，假設我們在 ads_date 列上有一個索引， 並且希望獲取自某個日期以來的廣告數量 ，如下所示：
SELECT count (*) FROM advertisement WHERE advertisement_date >= <certain_date>;
在前面的查詢中，可以通過使用索引掃描或使用基於選擇性的順序掃描（取決於所提供的 sure_date 值）從硬碟中 獲取廣告表中 的條目。緩存這樣的查詢的執行計劃 會導致嚴重的問題。因此，編寫以下函數不是一個好主意：
CREATE OR REPLACE FUNCTION get_advertisement_count
(some_date timestamptz ) RETURNS BIGINT AS $$

BEGIN

RETURN (SELECT count (*) FROM advertisement WHERE advertisement_date >=some_date)::bigint;

END;

$$ LANGUAGE plpgsql;
要解決緩存問題，可以使用 SQL 語言函數或使用 PL / pgSQL execute 命令重寫以前的函數，如下所示：
CREATE OR REPLACE FUNCTION get_advertisement_count
(some_date timestamptz ) RETURNS BIGINT AS $$

DECLARE
count BIGINT;

BEGIN

EXECUTE 'SELECT count (*) FROM advertisement WHERE
advertisement_date >= $1' USING some_date INTO count;
RETURN count;

END;
$$ LANGUAGE plpgsql;
動態 SQL 使用的推薦做法
如果處理不當，動態 SQL 可能會導致安全性問題。動態 SQL 容易受到 SQL 注入技術的攻擊。SQL 注入用於執行揭示安全信息的 SQL 語句，甚至破壞資料庫中的數據。易受 SQL 注入攻擊的 PL / pgSQL 函數的一個非常簡單的示例如下：
CREATE OR REPLACE FUNCTION can_login (email text, pass text) RETURNS BOOLEAN AS $$

DECLARE
stmt TEXT;
result bool;

BEGIN

stmt = E'SELECT COALESCE (count(*)=1, false) FROM account WHERE email = ''|| $1 || E'' and password = ''||$2||E''';

RAISE NOTICE '%' , stmt;

EXECUTE stmt INTO result;

RETURN result;

END;
$$ LANGUAGE plpgsql;
如果電子郵件和密碼匹配，則前面的函數返回 true。為了測試此功能，讓我們插入一行並嘗試注入一些代碼，如下所示：
car_portal=> SELECT can_login('jbutt@gmail.com',md5('jbutt@gmail.com'));
NOTICE: SELECT COALESCE (count(*)=1, false) FROM account WHERE email ='jbutt@gmail.com' and password = '1b9ef408e82e38346e6ebebf2dcc5ece'

Can_login
-
t
(1 row)

car_portal=> SELECT can_login('jbutt@gmail.com',md5('jbutt@yahoo.com'));

NOTICE: SELECT COALESCE (count(*)=1, false) FROM account WHERE email ='jbutt@gmail.com' and password = '37eb43e4d439589d274b6f921b1e4a0d'
can_login
-
f

(1 row)

car_portal=> SELECT can_login(E'jbutt@gmail.com'--', 'Do not know password');

NOTICE: SELECT COALESCE (count(*)=1, false) FROM account WHERE email ='jbutt@gmail.com'--' and password = 'Do not know password'

can_login
-
t
(1 row)
請注意，即使密碼與表中存儲的密碼不匹配，該函數也會返回 true。這僅僅是因為謂詞已被注釋，如引發通知所示：
SELECT COALESCE (count(*)=1, false) FROM account WHERE email ='jbutt@gmail.com'--' and password = 'Do not know password'
為了保護代碼不受這種技術的影響，可以遵循以下做法：
對於參數化的動態 SQL 語句，請使用 USING 子句。使用格式函數和適當的插值來構造查詢。請注意，％I 將參數轉義為標識符，而將％L 轉義為文字。使用quote_ident()，quote_literal()和quote_nullable()正確格式化標識符和文字的格式。
編寫上述功能的一種方法如下：
CREATE OR REPLACE FUNCTION can_login (email text, pass text) RETURNS BOOLEAN AS $$

DECLARE
stmt TEXT;
result bool;

BEGIN

stmt = format('SELECT COALESCE (count(*)=1, false) FROM account WHERE email = %Land password = %L', $1,$2);

RAISE NOTICE '%' , stmt;

EXECUTE stmt INTO result;

RETURN result;

END;
$$ LANGUAGE plpgsql;
我們看到了如何動態地動態使用 SQL 來構建和執行查詢。與靜態 SQL 語句不同，動態 SQL 語句的全文是未知的，並且可以在連續執行之間進行更改。這些查詢可以是 DDL，DCL 和(或)DML 語句。
原文：How to implement Dynamic SQL in PostgreSQL 10[1]
作者: Amey Varangaonkar
參考資料[1]
How to implement Dynamic SQL in PostgreSQL 10: https://hub.packtpub.com/how-to-implement-dynamic-sql-in-postgresql-10/

相關焦點

  • 極致性能 sqltoy-orm-4.12.10 發版 - OSCHINA - 中文開源技術交流...
    主鍵策略 修復postgresql bytea類型轉換錯誤 優化quickvo增加postgresql類型默認匹配開源地址：sqltoy特點說明： 支持mysql、postgresql、db2、oracle、sqlserver、sqlite、clickhouse、elasticsearch、mongodb等 具有JPA模式的CRUD功能(即CRUD無需寫sql)，無需寫Dao，sqltoy提供了SqlToyLazyDao，同時提供了quickvo從資料庫生成POJO。
  • PostgreSQL函數
    /docs/12/xfunc-sql.html4.基於pl/pgsql的存儲過程pl/pgsql是postgresql資料庫系統的一個可加載的過程語言。因此命令字符串可以在函數裡動態的生成以便於對各種不同的表和欄位進行操作,從而提高函數的靈活性。然而由此換來的卻是性能上的折損。
  • PostgreSQL Tips:DATEDIFF
    SELECT DATEDIFF('second', '08:54:55'::time, '08:56:10'::time);  -- Result: 75參考資料[1]PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc: http://www.sqlines.com/postgresql
  • 極致查詢性能 sqltoy-orm-4.12.6 發版 - OSCHINA - 中文開源技術...
    /tree/master/trunk/sqltoy-nosql見test下面MongoTest.javasqltoy特點說明： 支持mysql、postgresql、db2、oracle、sqlserver、sqlite、clickhouse、elasticsearch、mongodb等
  • 滲透中利用postgresql getshell及注入技巧
    3.B站在線學習連結由於分享的資源連結經常被和諧，所以必須遷移到B站上供大家學習B站up主官方帳號：VMYKnetwork團隊0x00 前言研究postgresql資料庫如何getshell是在滲透中遇到一個pgAdmin的web管理頁面可以直接操作postgresql且通過網上的文章沒有達到9.6版本getshell的效果所以便有了以下文章。
  • 極致查詢性能 sqltoy-orm-4.12.8 發版增加對國產達夢資料庫支持
    特點說明： 支持mysql、postgresql、db2、oracle、sqlserver、sqlite、clickhouse、elasticsearch、mongodb等 具有JPA模式的CRUD功能(即CRUD無需寫sql)，無需寫Dao，sqltoy提供了SqlToyLazyDao，同時提供了
  • mybatis使用註解編寫動態sql注意事項
    在spring+springmvc+mybatis框架中開發，編寫sql語句以前都是直接使用xml的方式編寫，現在由於新的項目框架搭建過程中，同事配置的是使用註解的方式編寫sql語句，因此我也開始學習使用註解編寫sql語句。
  • MyBatis 動態 SQL 詳解(以後寫 SQL 爽多了)
    動態 SQL 是 MyBatis 的強大特性之一。如果你使用過 JDBC 或其它類似的框架，你應該能理解根據不同條件拼接 SQL 語句有多痛苦，例如拼接時要確保不能忘記添加必要的空格，還要注意去掉列表最後一個列名的逗號。利用動態 SQL，可以徹底擺脫這種痛苦。
  • MyBatis dynamic SQL 1.1.4 發布,生成動態 SQL 的框架
    MyBatis dynamic SQL 1.1.4 已發布，MyBatis Dynamic SQL 是生成動態 SQL 語句的框架，可把它看作是一個類型安全的 SQL 模板庫，它還支持 MyBatis3
  • Myql SLEEP函數和SQL注入
    sqlmap是使用Python編寫的一款資料庫sql注入掃描工具，目前支持常見的mysql、oracel、postgresql、sql server，access，db2，sqlite等數據的安全漏洞（sql注入）。
  • 優雅的 ORM 框架 sqltoy-orm-4.11.9 發版了
    因為針對常規的CRUD sqltoy跟大家並無較大差異！如果您的數據規模較大，涉及相對複雜的查詢已經影響到了用戶體驗，可以深入了解sqltoy，對你會有較大的幫助！,，給開發更多信息4、查詢返回結果支持List<Object[]>,同時支持resultType 直接給Map.class等接口（之前必須是HashMap等實現類）、5、quickvo支持yml格式的配置文件6、增強sql執行輸出#xml配置模式<bean id="sqlToyContext" name="sqlToyContext
  • sqltoy-orm-4.15.7.3 發版,增強 link 功能,開放緩存管理接口
    >2、優化postgresql9.4 版本的saveOrUpdate功能(9.4 不支持insert table AS T1別名模式，剔除別名)，推薦9.5+版本3、增強loadBySql、load(entity)對象類型處理，避免new VO(){{setId("");}} 雙大括號極端特殊場景下定義對象導致類型獲取錯誤。
  • Mybatis 動態sql語句if和where標籤巧妙使用
    本文章描述的是 Mybatis if標籤和where標籤的結合使用需求是：根據電話和名字查用戶數據1 sql 查詢查詢sql語句如下：SELECT id , gender , nickname , mobile , avatar FROM dts_user WHERE gender = 1 AND mobile LIKE '%456%'查詢結果如下圖所示：2 mybatis動態sql配置
  • Python+SQL無敵組合,值得你擁有!
    我們也可以用sqlalchemy庫連接，代碼如下：from sqlalchemy import create_engineengine = create_engine('postgresql://postgres:password@localhost:5432/postgres')
  • 比mybatis 強大優雅的 sqltoy-orm-4.11.6 發版了
    sqltoy特性說明： 支持mysql、postgresql、db2、oracle、sqlserver、sqlite、clickhouse、elasticsearch等 具有JPA模式的CRUD功能(即CRUD無需寫sql)，無需寫Dao，sqltoy提供了SqlToyLazyDao
  • 去哪兒PostgreSQL指南
    主要內容命名規範Column設計Constraints 設計Index 設計關於NULL開發相關規範管理相關規範1 命名規範1.DB object: database, schema, table
  • SQL Server 動態行轉列(參數化表名、分組列、行轉列欄位、欄位值)
    今天跟大家分享SQL Server 動態行轉列（參數化表名、分組列、行轉列欄位、欄位值）的知識。一.本文所涉及的內容（Contents）本文所涉及的內容（Contents）背景（Contexts）實現代碼（SQL Codes）方法一：使用拼接SQL，靜態列欄位；方法二：使用拼接SQL，動態列欄位；方法三：使用PIVOT關係運算符，靜態列欄位；方法四：使用PIVOT關係運算符
  • Mybatis 動態sql 學習總結
    利用動態 SQL 這一特性可以徹底擺脫這種痛苦。 mybatis 的動態sql語句是基於OGNL表達式的。可以方便的在 sql 語句中實現某些邏輯.where (主要是用來簡化sql語句中where條件判斷的，能智能的處理 and or ,不必擔心多餘導致語法錯誤)　　5. set (主要用於更新時)　　6.
  • 將函數索引從Oracle遷移到PostgreSQL
    CREATE INDEX idx_cust_id_i ON hb_e2e.ftp_dipan USING BTREE (aws_oracle_ext.substr(immutable_to_char(cust_id::numeric),lengthb(immutable_to_char(cust_id::numeric)) ) ASC);再次執行asc排序類的sql