Postgresql动态SQL

全栈程序员-用户IM • 2022年6月23日上午6:46 • 未分类

大家好，又见面了，我是你们的朋友全栈君。

作者：瀚高PG实验室（Highgo PG Lab）

PostgreSQL支持动态SQL，以PL/Pgsql为例，语法如下：
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, … ] ];
上式中的可选项target表示a record variable, a row variable, or a comma-separated list of simple variables and record/row fields。如果要返回结果集，那么需要用到RETURN QUERY的一个变形：RETURN QUERY EXECUTE command-string [ USING expression [, … ] ]; 参数表达式可以通过USING插入到计算查询字符串中，以EXECUTE命令的同样方式。
PostgreSQL也提供了一些字符串处理函数，可以更方便地拼接字符串。
quote_ident：Return the given string suitably quoted to be used as an identifier in an SQL statement string。根据sql语句返回给定的标识符，字符串是表名列名等标识数据库对象时候有用
quote_literal：Return the given string suitably quoted to be used as a string literal in an SQL statement string.对特殊字符进行转义。
quote_nullable：当传入参数可能为null时，可使用quote_nullable，而不是quote_literal。前者返回字符串格式的’Null’，后者返回的就是Null。pg中所有东西与null比较返回的都是null。
format：EXECUTE format(‘UPDATE tbl SET %I = %L WHERE key = %L’, colname, newvalue, keyvalue); or EXECUTE format(‘UPDATE tbl SET %I = 1 WHEREkey=2’, colname) USING newvalue, keyvalue; 后者更有效率，因为关键词比较时不会出现隐式转换。注意format的格式化类型字符s, I, L. 分别表示字符串, identified, 和literal（注意s、L不要搞反了）。示例：
CREATE OR REPLACE FUNCTION func_get_merchandises(
keyword text,
isinland boolean,
startindex integer DEFAULT 0,
takecount integer DEFAULT 20,
sortfield text DEFAULT ‘MerchandiseName’::text,
sortorder text DEFAULT ‘asc’::text)
RETURNS SETOF “Merchandises” AS
$BODY$
begin
return query EXECUTE
format(‘select m.* from “Merchandises” m
where m.tsv @@ plainto_tsquery($1) and m.”IsInland”=$2
order by %I %s limit $3 offset $4′,sortfield,sortorder) using keyword,isinland,takecount,startindex;
end
$BODY$