动态SQL和RETURN
类型
(继续阅读,我为您留了最好的内容!)
您想执行动态SQL。在plpgsql中,借助EXECUTE
,这很简单。您不需要一个游标。事实上,在大多数情况下,没有显式的游标更好。
您遇到的问题是:您想返回未定义类型的记录。函数需要在RETURNS
子句(或使用OUT
或INOUT
参数)中声明其返回类型。在您的情况下,您必须退回到匿名记录,因为返回列的数字、名称和类型是不确定的。例如:
CREATE FUNCTION data_of(integer)
RETURNS SETOF record AS ...
然而,这并不是特别有用的。您必须在每次调用时提供一个列定义列表。例如:
SELECT * FROM data_of(17)
AS foo (colum_name1 integer
, colum_name2 text
, colum_name3 real);
但是如果你不知道列名,该怎么办呢?
你可以使用 less structured document 数据类型,例如 json
, jsonb
, hstore
或 xml
。请参见:如何在数据库中存储数据表?
但是,对于这个问题的目的,让我们假设您想尽可能返回单独,正确命名和类型的列。
固定返回类型的简单解决方案
列 datahora
似乎是一个给定的列,我将假设其数据类型为 timestamp
并且始终有两列名称和数据类型变化。
名称 我们会放弃而采用返回类型中的通用名称。
类型 我们也会放弃,并将所有类型转换为 text
,因为 每种 数据类型都可以转换为 text
。
CREATE OR REPLACE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
LANGUAGE plpgsql AS
$func$
DECLARE
_sensors text := 'col1::text, col2::text';
_type text := 'foo';
BEGIN
RETURN QUERY EXECUTE '
SELECT datahora, ' || _sensors || '
FROM ' || quote_ident(_type) || '
WHERE id = $1
ORDER BY datahora'
USING _id;
END
$func$;
变量 _sensors
和 _type
可以作为输入参数。
注意 RETURNS TABLE
子句。
注意使用 RETURN QUERY EXECUTE
。 这是从动态查询返回行的更优雅的方式之一。
我给函数参数取了一个名称,只是为了使RETURN QUERY EXECUTE
的USING
子句不那么令人困惑。SQL字符串中的$1
不是指函数参数,而是指通过USING
子句传递的值。 (在这个简单的示例中,它们两个都恰好在各自的范围内是$1
。)
注意 _sensors
的示例值:每列都被转换为类型 text
。
这种代码非常容易遭受SQL注入攻击。 我使用quote_ident()
来防止它。 在变量_sensors
中组合几个列名会导致无法使用quote_ident()
(通常也是一个坏主意!)。 确保通过将每个列名分别运行通过quote_ident()
,从而通过其他方式确保其中不会出现错误的内容。可以考虑使用 VARIADIC
参数...
自PostgreSQL 9.1以来更简单
从9.1或更高版本开始,您可以使用format()
来进一步简化:
RETURN QUERY EXECUTE format('
SELECT datahora, %s -- identifier passed as unescaped string
FROM %I -- assuming the name is provided by user
WHERE id = $1
ORDER BY datahora'
,_sensors, _type)
USING _id;
再次强调,正确转义各个列名是一种干净的方式。
多个具有相同类型的列
在您的问题更新后,看起来返回结果的列数是不确定的,但所有列都是 double precision
(别名 float8
)类型。
- 可以使用
ARRAY
类型来嵌套变量数量的值。 - 此外,我将返回一个包含列名的数组:
CREATE OR REPLACE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
LANGUAGE plpgsql AS
$func$
DECLARE
_sensors text := 'col1, col2, col3';
_type text := 'foo';
BEGIN
RETURN QUERY EXECUTE format('
SELECT datahora
, string_to_array($1) -- AS names
, ARRAY[%s] -- AS values
FROM %s
WHERE id = $2
ORDER BY datahora'
, _sensors, _type)
USING _sensors, _id;
END
$func$;
各种完整的表格类型
要实际返回一个表的所有列,有一种简单而强大的解决方案,即使用多态类型:
CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE format('
SELECT *
FROM %s -- pg_typeof returns regtype, quoted automatically
WHERE id = $1
ORDER BY datahora'
, pg_typeof(_tbl_type))
USING _id;
END
$func$;
重要提示:请务必拨打以下电话:
SELECT * FROM data_of(NULL::pcdmet, 17);
在调用中将pcdmet
替换为任何其他表名。
这是如何工作的?
anyelement
是一种伪数据类型,多态类型,是任何非数组数据类型的占位符。函数中所有出现的anyelement
在运行时都会计算为相同的类型。通过向函数提供定义类型的值作为参数,我们隐含地定义了返回类型。
PostgreSQL自动为创建的每个表定义一个行类型(复合数据类型),因此对于每个表都有一个明确定义的类型。这包括临时表,这对于临时使用非常方便。
任何类型都可以是NULL
。将一个NULL
值交给表类型进行强制转换:NULL::pcdmet
。
现在函数返回了一个明确定义的行类型,我们可以使用SELECT * FROM data_of()
来分解行并获取各个列。
pg_typeof(_tbl_type)
以对象标识符类型regtype
的形式返回表的名称。当自动转换为text
时,如果需要,标识符会自动加上双引号和架构限定符,自动防御SQL注入。这甚至可以处理带有模式限定表名的情况,而quote_ident()
会失败。请参见: