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.