1

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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JimmyBuffet_Express
  • 290
  • 5
  • 19
  • 1
    1) You can simplify this with ` Select count(*) into RowCount ...` and eliminate the sub-query 2) Look at [Dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). – Adrian Klaver Sep 27 '21 at 14:22

1 Answers1

1

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;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44