PostgreSQL11中实现动态SQL

动态SQL用于减少查询时的重复任务，通过允许有效的数据查询来简化数据库程序员的工作。

例如，可以使用动态SQL每天为某个表创建表分区，在所有外键上添加缺少的索引，或者向某个表添加数据审核功能，而不会产生主要的编码效果。

动态SQL的另一个重要用途是克服PL/pgSQL缓存的副作用，因为使用执行语句未缓存。

动态 SQL 是通过 EXECUTE 语句实现的。该 EXECUTE 语句接受一个字符串，并简单地计算它。执行语句的摘要如下：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ...]];

在动态 SQL 中执行 DDL 语句

• DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

某些情况下，需要在数据库对象级别执行操作，例如表，列，索引等。例如，数据库开发人员希望清理并分析特定的架构对象，目的是更新统计信息。如，要分析 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 语句

• DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的关键字主要包括 insert、delete、udpate 和select 等(增添改查）

某些应用程序可能以交互方式与数据交互。如某些应用程序会根据用户定义的不同标准过滤从而返回数据。在这种情况下，动态SQL使用动态谓词获取数据非常方便，如下所示：

--- predicate 谓词
---setof 表名:创建函数get_bus_data()，它返回bus_data表中满足条件的数据，返回值声明为 setof 表名	
		
CREATE OR REPLACE FUNCTION get_bus_data (predicate TEXT)
RETURNS SETOF bus_data AS $$

BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM bus_data WHERE ' || predicate;
END;
$$ 
LANGUAGE plpgsql;
 

 

动态 SQL 及其缓存效果 

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 语句，请使用 USING 子句

参考文献：https://hub.packtpub.com/how-to-implement-dynamic-sql-in-postgresql-10/