Save dynamic query to variable in postgres stored procedure

Question

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;

1) You can simplify this with ` Select count(*) into RowCount ...` and eliminate the sub-query 2) Look at Dynamic SQL. — Adrian Klaver
– Adrian Klaver, Commented Sep 27, 2021 at 14:22

Jim Jones · Accepted Answer · 2021-09-27 14:29:17Z

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;

edited Sep 27, 2021 at 14:29

answered Sep 27, 2021 at 14:21

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Save dynamic query to variable in postgres stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related