参数值被视为数据类型- PL/pgSQL

Question

我刚开始使用PL/pgSQL。在过去的三年里，我一直是一名SQL Server开发人员。我正在尝试使用动态SQL编写一个函数，传入一个表名，然后传回一个值和该表名。

我仍然在尝试如何在PostgreSQL中编写存储的过程和函数，但是我找不到任何文档来做这样的事情。

CREATE OR REPLACE FUNCTION exc_tables (tname VARCHAR)
RETURNS TABLE (module_id bigint, tablename varchar) AS $$
BEGIN
  RETURN QUERY EXECUTE format('SELECT DISTINCT module_id, '||CAST(tname AS varchar)||' as tablename FROM ' || tname || ' WHERE module_ID <> 6');

END;
$$
LANGUAGE plpgsql;

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type dt_conn_type does not match expected type character varying in column 2.
  Where: PL/pgSQL function exc_tables(character varying) line 3 at RETURN QUERY

Answer 1

您需要将cast()放入查询中。

...
RETURN QUERY EXECUTE format('SELECT DISTINCT module_id, CAST('''||tname||''' AS varchar) as tablename FROM ' || tname || ' WHERE module_ID <> 6');
...

但由于它已经是一个varchar，所以强制转换是无用的，您可以只使用：

RETURN QUERY EXECUTE format('SELECT DISTINCT module_id, '''||tname||''' as tablename FROM ' || tname || ' WHERE module_ID <> 6');

但您需要确保在查询中将其括在单引号中，以使其成为字符串文字，否则它意味着整个记录(它的类型是tname，所以不是varchar)。

顺便说一句，使用只有一个无占位符的格式字符串和没有替换参数的format()也是非常无用的。