How to call procedure with IN and INOUT values in Postgres Procedure

Question

I have migrated oracle databases to Aurora Postgresql on RDS AWS, With the help of AWS SCT(Schema Conversion Tool). Some of the Packages in Oracle converted to postgres in below format.

CREATE OR REPLACE PROCEDURE miptd."pkg_rpt$strptst"(i_molo text,INOUT o_cursor refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE

    o_cursor$ATTRIBUTES aws_oracle_data.TCursorAttributes;
    
BEGIN   
    o_cursor := NULL;
    OPEN o_cursor FOR
    
    EXECUTE CONCAT_WS('','select count(ID) From 
                        (select DISTINCT D.ID, B.CSS_DISTRICT, B.RDA, C.TECH_TYPE, C.FORECAST_DATE, D.SITE_TYPE
                        FROM
                        ALB.VIEWSIRPT B, 
                        ALB.VIEWWRDATA C,
                        ALB.', i_molo, ' D
                        WHERE
                        (D.CSR = C.CSR) AND (D.CUSTOMER = C.CUSTOMER)
                        AND (D.CSR = B.CSR ) AND (D.CUSTOMER = B.CUSTOMER )  )a ');

    o_cursor$ATTRIBUTES := ROW (TRUE, 0, NULL, NULL);
END;
$BODY$;

Not aware of cursors usage in postgres How do I call the procedure. ? Where do i see my result value by calling the procedure? Is it ok to convert this as a function ?

Answer 1

You should use a function, and you should avoid SQL injection:

CREATE function some_name(i_molo text) RETURNS bigint
   LANGUAGE plpgsql AS
$$BEGIN
   RETURN EXECUTE
      format('SELECT count(ID)
              FROM (SELECT DISTINCT D.ID,
                                    B.CSS_DISTRICT,
                                    B.RDA, C.TECH_TYPE,
                                    C.FORECAST_DATE,
                                    D.SITE_TYPE
                    FROM ALB.%I D
                       JOIN ALB.VIEWWRDATA C
                          ON D.CUSTOMER = C.CUSTOMER AND D.CSR = C.CSR
                       JOIN ALB.VIEWSIRPT B
                          ON D.CUSTOMER = B.CUSTOMER AND D.CSR = B.CSR
                   ) AS a',
              i_molo);
END;$$;