PostgreSQL Tips: 动态SQL

前言

前几天 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

参考资料