Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

merlin in set returning function i have three out parameter returning same
type of object so what

to do in that case if i am using only return next than i get all output in
one set of record so in

the application end i can not distinguish what output is of what parameter .

i want in output like

op_dimlist ,op_freqlist ,op_svrlist
and
i also want to access like select op_dimlist.DD_DIMID from
morse_new_sit.proc_get_freq_svrty(10,10,2,1) and something like that.

CREATE TYPE "user_fs_obj" AS (

DD_DIMID bigint,
DD_DIMNAME varchar(20),
dd_dimcolorcd varchar(10)
);

CREATE OR REPLACE FUNCTION "morse_new_sit"."proc_get_freq_svrty"(IN
ip_dim_type bigint, IN ip_type bigint, IN ip_frqsubype bigint, IN
ip_svrsubtype bigint,
op_dimlist OUT morse_new_sit.user_fs_obj,op_freqlist OUT
morse_new_sit.user_fs_obj,op_svrlist OUT morse_new_sit.user_fs_obj) RETURNS
SETOF morse_new_sit.user_fs_obj AS $BODY$

DECLARE

op_dimlist morse_new_sit.user_fs_obj%rowtype;
op_freqlist morse_new_sit.user_fs_obj%rowtype;
op_svrlist morse_new_sit.user_fs_obj%rowtype;

BEGIN
RAISE NOTICE 'GET DIM DETAILS';
-- Get the DIM details
FOR op_dimlist IN
SELECT DD_DIMID,dd_dimname,dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = IP_DIM_TYPE
AND DD_STATUS = 0
AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
DD_VALIDTO
LOOP

proc_get_freq_svrty.op_dimlist = op_dimlist;
return next;
RAISE NOTICE 'OP_DIM_LIST %',op_dimlist;
END LOOP;
--Return op_dimlist_array;

-- GET the FREQ details
FOR op_freqlist IN
SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = ip_type
AND DD_DIMSUBTYPE = ip_frqsubype
AND DD_STATUS = 0
AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
DD_VALIDTO
LOOP

RAISE NOTICE 'op_freqlist LIST %',op_freqlist;
proc_get_freq_svrty.op_freqlist = op_freqlist;
return next;
END LOOP;

--Get the Severity
FOR op_svrlist IN
SELECT DD_DIMID,dd_dimname, dd_dimcolorcd FROM morse_new_sit.DIM_DEF
WHERE DD_DIMTYPE = ip_type

AND DD_DIMSUBTYPE = ip_svrsubType

AND DD_STATUS = 0

AND date_trunc('day', LOCALTIMESTAMP) BETWEEN DD_VALIDFROM AND
DD_VALIDTO
LOOP

RAISE NOTICE 'op_svrlist LIST %',op_svrlist;
proc_get_freq_svrty.op_svrlist = op_svrlist;
return next;
END LOOP;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;

Thanks for ur help merlin

--
View this message in context: http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5714521.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.