Save dynamic query to variable in postgres stored procedure-CodePudding

I have the following postgres stored procedure:

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;

which "prints" out the row count of the static table MySchema.MyTable. How can it make it so I pass the Table and Schema name as an input.

eg:

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;

CodePudding user response：

You should use format() instead of concatenating the strings with || and then EXECUTE ... INTO to get the query's result, e.g.

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;