'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 |
|---|
