将动态查询保存到postgres存储过程中的变量中

Question

我有以下postgres存储过程:

CREATE OR REPLACE PROCEDURE
schema.MyProcedure()
AS $$
DECLARE 
RowCount int;

BEGIN

SELECT cnt INTO RowCount 
FROM (
SELECT COUNT(*) AS cnt
FROM MySchema.MyTable
) AS sub;

RAISE NOTICE 'RowCount: %', RowCount;

END;
$$
LANGUAGE plpgsql;

"prints"输出静态表MySchema.MyTable的行数。怎么才能让我传递表名和Schema名作为输入呢?

,

CREATE OR REPLACE PROCEDURE
schema.MyProcedure(MySchema_In varchar, MyTable_In varchar)
AS $$
DECLARE 
RowCount int;

BEGIN

SELECT cnt INTO RowCount 
FROM (
SELECT COUNT(*) AS cnt
FROM || **MySchema_In** || . || **MyTable_In** || 
) AS sub;

RAISE NOTICE 'RowCount: %', RowCount;

END;
$$
LANGUAGE plpgsql;

Answer 1

您应该使用format()而不是将字符串与||然后EXECUTE ... INTO连接以获得查询结果，例如

CREATE OR REPLACE PROCEDURE MyProcedure(MySchema_In varchar, MyTable_In varchar)
AS $$
DECLARE RowCount int;
BEGIN
EXECUTE FORMAT('SELECT count(*) FROM %I.%I',$1,$2) INTO RowCount;
RAISE NOTICE 'RowCount: %', RowCount;    
END;
$$
LANGUAGE plpgsql;