'PostgreSQL Function returning only one row

I am writing a Function to accept a list by a parameter and return some set of records. When I run the select query alone, it is showing all the rows. But from Function I'm getting only the top row. Still searching about an hour, didn't get solutions.

Here is the Function query

CREATE OR REPLACE FUNCTION get_transaction_all_property(p_property character varying, p_year timestamp without time zone)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE
  l_num_property  numeric(15,2);
  l_num_strtamt numeric(15,2);
  l_num_endamt  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_strtamt, l_num_endamt 
    property, coalesce(sum(strtamt),0)::numeric(15,2), coalesce(sum(endamt),0)::numeric(15,2) from (
    (select a.property as property, SUM(b.strtamtg + b.strtamtl) AS strtamt, SUM(b.endamtg + b.endamtl) AS endamt 
    FROM "myTransactions" AS a 
    WHERE a.property::text = ANY(STRING_TO_ARRAY(l_property,',')) AND a.period < l_year
      group by a.property)
)as doo group by property;
  SELECT INTO result l_num_property, l_num_strtamt, l_num_endamt;
  RETURN next result;
END;
$function$
;

-- Permissions

ALTER FUNCTION get_transaction_all_property(varchar,timestamp,int8) OWNER TO mysuer;
GRANT ALL ON FUNCTION get_transaction_all_property(varchar,timestamp,int8) TO mysuer;

Here is the Function Call from SSRS:

select * from get_transaction_fund_totals_year_recon_sf_new(?,?)  as ("property" numeric, "initial" numeric, "end" numeric)

SSRS Parameter Expression:

=Join(Parameters!pty.Value,",")
=Join(Parameters!dat.Value,",")

Please any one guide me to do this.

Thanks in Advance



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source