sql - 创建返回表大小的函数

Question

我的代码:

CREATE OR REPLACE FUNCTION sizeOfTableFunction
(
  p_tableName varchar(100)
)
RETURNS integer 
AS $$
DECLARE
  p_tableSize integer;
BEGIN
  SELECT count(*) into p_tableSize from p_tableName;
  return p_tableSize;
END;
$$ LANGUAGE plpgsql STRICT;

函数已正确创建:

CREATE FUNCTION

执行:

SELECT * FROM sizeOfTableFunction('Run');

输出 - 执行函数时出现问题？:

mydb=> SELECT * FROM sizeOfTableFunction('Run');
ERROR:  relation "p_tablename" does not exist
LINE 1: SELECT count(*)                  from p_tableName
                                              ^
QUERY:  SELECT count(*)                  from p_tableName
CONTEXT:  PL/pgSQL function "sizeoftablefunction" line 5 at SQL statement

Answer 1

为此你需要动态 SQL:

CREATE OR REPLACE FUNCTION sizeOfTableFunction
(
  p_tableName varchar(100)
)
RETURNS integer 
AS $$
DECLARE
  p_tableSize integer;
BEGIN
  execute 'SELECT count(*) from '||p_tableName into p_tablesize; -- this is the difference
  return p_tableSize;
END;
$$ LANGUAGE plpgsql STRICT;

为了安全起见，最好使用quote_ident 函数，以防您的表名包含特殊字符。它还为您提供一些保护，防止 SQL 注入(inject)。

execute 'SELECT count(*) from '||quote_ident(p_tableName) into p_tablesize;