postgresqlstored-proceduresplpgsqldynamicquery

Save dynamic query to variable in postgres stored procedure


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;

Solution

  • 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;