动态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/