I want to mock a PostgresQL database for my unit tests, but I have several stored procedures in use.

How do I implement it in the pg-mem package and the stored procedure has a database schema that is used?

This is an example of a stored procedure:

CREATE OR REPLACE FUNCTION trx.report_action_history_preq(
    var_username character varying,
    var_start timestamptz,
    var_end timestamptz
)
RETURNS TABLE(
    id integer,
    submission_number character varying,
    vendor_name character varying,
    status integer,
    approved_on character varying,
    submitted_date timestamptz,
    created_by character varying,
    approved_by character varying,
    reviewed_date timestamptz
)
LANGUAGE plpgsql
AS $function$
BEGIN
    -- Create a temporary table to store intermediate results
    CREATE TEMP TABLE temp_result (
        id INT, 
        submission_number VARCHAR,
        vendor_name VARCHAR,
        status INT,
        approved_on VARCHAR,
        submitted_date TIMESTAMPTZ,
        created_by VARCHAR,
        approved_by VARCHAR,
        reviewed_date TIMESTAMPTZ
    ) ON COMMIT DROP;

    -- Insert the first query result into the temporary table
    INSERT INTO temp_result
    SELECT 
        sh.id,
        sh.submission_number,
        sh.vendor_name,
        sh.status,
        'DOCUMENT COLLECTION'::VARCHAR AS approved_on,
        sh.submitted_date::timestamptz,
        sh.created_by,
        sh.approved_by,
        sh.approved_on::timestamptz
    FROM trx.submission_header sh
    WHERE sh.approved_by = var_username AND sh.approved_on BETWEEN var_start AND var_end;

    INSERT INTO temp_result
    SELECT 
        sh.id,
        sh.submission_number,
        sh.vendor_name,
        sh.status,
        'DOCUMENT REVIEW'::VARCHAR AS approved_on,
        sh.submitted_date::timestamptz,
        sh.created_by,
        spr.username,
        spr.created_on::timestamptz
    FROM trx.submission_header sh
    JOIN trx.submission_process_review spr ON sh.id = spr.submission_header_id
    WHERE spr.username = var_username AND spr.created_on BETWEEN var_start AND var_end;

    -- Return all rows from the temporary table
    RETURN QUERY SELECT * FROM temp_result;
END;
$function$
;

enter image description here

I hope I don’t want any changes to my query, and only changes to the use of the stored procedure in the pg-mem package