How to pass an array or list to a stored procedure in PostgreSQL-CodePudding

I am trying to develop a stored procedure/function in PostgreSQL to accept and process a list from SSRS. I have some issues in passing the list to the stored procedure. Here is the query

CREATE OR REPLACE FUNCTION get_transaction_all_accounts(p_property[] varchar, p_year timestamp without time zone)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
DECLARE
  l_num_property numeric(15,2);
  l_num_amount numeric(15,2);
  l_property   varchar   := '';
  l_year    timestamp := LOCALTIMESTAMP;
  result RECORD;
BEGIN
  IF ( p_property IS NOT NULL ) THEN
    l_property := p_property;
  END IF;
  IF ( p_year IS NOT NULL ) THEN
    l_year := p_year;
  END IF;
  SELECT INTO l_num_property, l_num_amount 
  property::numeric(15,2), coalesce(sum(amount),0)::numeric(15,2) from (
    select a.propertyname AS property, a.amount 
    FROM sw."fTransaction" a
    WHERE a.propertyname = l_property
  )as foo;
  SELECT INTO result l_num_property, l_num_amount;
  RETURN result;
END;
$function$
;

-- Permissions

ALTER FUNCTION get_transaction_all_accounts(varchar,timestamp) OWNER TO myuser;
GRANT ALL ON FUNCTION get_transaction_all_accounts(varchar,timestamp) TO myuser;

Here is the dataset query

select sum(amount) as sink from get_transaction_all_accounts(?,?, -1)  as ("property" numeric, "amount" numeric)

Parameter expressions I used:

Join(Parameters!property.Value,",")
DateAdd("d", 1, Parameters!date.Value)

I don't know how to process the query to pass the list data to the function parameter. Please help me to do this

Thanks

CodePudding user response：

Function with comma separated input value and other value and prepare dynamic query where few input param (if provided) and use regexp_split_to_table for splitting comma separated value into list. If null or empty string ('') is used as input value then related column name isn't used in searching criteria.

-- PostgreSQL (v14)
-- Function: public.rpt_member_info(character varying, character varying, character varying, character varying, character varying)

DROP FUNCTION IF EXISTS public.rpt_member_info(character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION public.rpt_member_info(
    IN p_officeInfoId CHARACTER VARYING,
    IN p_projectInfoId CHARACTER VARYING,
    IN p_memberInfoId CHARACTER VARYING,
    IN p_fromDate CHARACTER VARYING,
    IN p_toDate CHARACTER VARYING,
    OUT member_id BIGINT,
    out office_info_id bigint,
    out project_info_id bigint,
    OUT member_no CHARACTER VARYING,
    OUT member_name CHARACTER VARYING,
    OUT membership_date TIMESTAMP WITHOUT TIME ZONE)
  RETURNS SETOF record AS
$BODY$

-- declare local variables
DECLARE v_prepareQuery         VARCHAR(21840) DEFAULT '';
    v_officeInfo           VARCHAR(150) DEFAULT '';
    v_projectInfo          VARCHAR(150) DEFAULT '';
    v_memberInfo           VARCHAR(150) DEFAULT '';
    refcur refcursor default 'test';

BEGIN
    
    /**************************History**************************/
    -- Name : rpt_member_info
    -- Created Date (dd/MM/yyyy): 01/02/2022 
    -- Created By : Rahul Biswas
    -- Reason : 
    -- Execute : SELECT * FROM rpt_member_info('101', '1', '1', '2022-01-01', '2022-01-31');
    /***********************************************************/

    -- prepare variable data based on given input
    IF(p_officeInfoId != '' AND p_officeInfoId != 'null' AND p_officeInfoId IS NOT NULL)
    THEN
        v_officeInfo = CONCAT(' AND mi.office_info_id = ANY(SELECT CAST(regexp_split_to_table(''', p_officeInfoId, ''',''', ', '') AS BIGINT))', CHR(10));
    END IF;

    IF(p_projectInfoId != '' AND p_projectInfoId != 'null' AND p_projectInfoId IS NOT NULL)
    THEN
        v_projectInfo = CONCAT(' AND mi.project_info_id = ', p_projectInfoId, CHR(10));
    END IF;

    IF(p_memberInfoId != '' AND p_memberInfoId != 'null' AND p_memberInfoId IS NOT NULL)
    THEN
        v_memberInfo = CONCAT(' AND mi.id = ', p_memberInfoId, CHR(10));
    END IF;

    -- prepare query
        v_prepareQuery := CONCAT('SELECT mi.id member_id
                                       , mi.office_info_id
                                       , mi.project_info_id
                                       , mi.member_no
                                       , mi.member_name
                                       , mi.membership_date
                                  FROM member_info mi
                                  WHERE 1 = 1', CHR(10)
                                  , v_officeInfo
                                  , v_projectInfo
                                  , v_memberInfo
                                  , 'AND mi.membership_date BETWEEN ', '''', p_fromDate, '''', ' AND ', '''', p_toDate, '''', ';');
 
 RETURN QUERY EXECUTE v_prepareQuery;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Execute this function

SELECT * FROM rpt_member_info('101, 102', '1', '', '2022-01-01', '2022-01-31');

Please check this url https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a38347cb181d0347a06ba6304e9448ef

If don't use dynamic query and supply comma separated value as input then use the below function

-- Function: public.rpt_member_info(character varying, bigint, TIMESTAMP WITHOUT TIME ZONE, bigint, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE)

DROP FUNCTION IF EXISTS public.rpt_member_info(character varying, bigint, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE);

CREATE OR REPLACE FUNCTION public.rpt_member_info(
    IN p_officeInfoId CHARACTER VARYING,
    IN p_projectInfoId BIGINT,
    IN p_fromDate TIMESTAMP WITHOUT TIME ZONE,
    IN p_toDate TIMESTAMP WITHOUT TIME ZONE,
    OUT member_id BIGINT,
    out office_info_id bigint,
    out project_info_id bigint,
    OUT member_no CHARACTER VARYING,
    OUT member_name CHARACTER VARYING,
    OUT membership_date TIMESTAMP WITHOUT TIME ZONE)
  RETURNS SETOF record AS
$BODY$

-- declare local variables
DECLARE v_prepareQuery         VARCHAR(21840) DEFAULT '';
    v_officeInfo           VARCHAR(150) DEFAULT '';
    v_projectInfo          VARCHAR(150) DEFAULT '';
    v_memberInfo           VARCHAR(150) DEFAULT '';
    refcur refcursor default 'test';

BEGIN
    
    /**************************History**************************/
    -- Name : rpt_member_info
    -- Created Date (dd/MM/yyyy): 01/02/2022 
    -- Created By : Rahul Biswas
    -- Reason : 
    -- Execute : SELECT * FROM rpt_member_info('101, 102', 1, '2022-01-01', '2022-01-31');
    /***********************************************************/

    -- execute query
        RETURN QUERY SELECT mi.id member_id
                                  , mi.office_info_id
                                  , mi.project_info_id
                                  , mi.member_no
                                  , mi.member_name
                                  , mi.membership_date
                             FROM member_info mi
                             WHERE mi.office_info_id = ANY(SELECT CAST(regexp_split_to_table(p_officeInfoId, ',') AS BIGINT))
                                  AND mi.project_info_id = p_projectInfoId
                                  AND mi.membership_date BETWEEN p_fromDate AND p_toDate;
 
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Execute the function

SELECT * FROM rpt_member_info('101, 102', 1, '2022-01-01', '2022-01-31');

Please use this url https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ee5b70bbb8fbeacbb9fad280cc775b58

N.B.: In lower version of Postgresql function and stored procedure are behave like same.