Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav <utsav.pshah@xxxxxxx>]

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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general