Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

CREATE OR REPLACE FUNCTION "morse_new_sit"."proc_get_freq_svrty_array1"(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[],op_clrlist OUT morse_new_sit.user_clr_obj[]
) RETURNS SETOF record AS $BODY$

DECLARE

op_dimlist morse_new_sit.user_fs_obj%rowtype;
op_dimlist_array morse_new_sit.user_fs_obj[];
op_freqlist morse_new_sit.user_fs_obj%rowtype;
op_freqlist_array morse_new_sit.user_fs_obj[];
op_svrlist morse_new_sit.user_fs_obj%rowtype;
op_svrlist_array morse_new_sit.user_fs_obj[];
op_clrlist morse_new_sit.user_clr_obj%rowtype;
op_clrlist_array morse_new_sit.user_clr_obj[];
m int;

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
    op_dimlist_array := array_append(op_dimlist_array,op_dimlist);
*proc_get_freq_svrty_array1.op_dimlist = op_dimlist_array;*
    RAISE NOTICE 'OP_DIM_LIST %',op_dimlist;
    END LOOP;
    m := array_length(op_dimlist_array, 1);
RAISE NOTICE ' array count ::: %',m;

--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
    op_freqlist_array := array_append(op_freqlist_array,op_freqlist);
    RAISE NOTICE 'op_freqlist LIST %',op_freqlist;
*    proc_get_freq_svrty_array1.op_freqlist = op_freqlist_array;*
    END LOOP;
    m := array_length(op_freqlist_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURN      op_freqlist_array;
    --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
    op_svrlist_array := array_append(op_svrlist_array,op_svrlist);
    RAISE NOTICE 'op_svrlist LIST %',op_svrlist;
*    proc_get_freq_svrty_array1.op_svrlist = op_svrlist_array;*
    END LOOP;
    m := array_length(op_svrlist_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURN      op_svrlist_array ;

        FOR OP_CLRLIST IN
        SELECT cs_second_scale, cs_first_scale,CS_COLOR_CODE FROM
morse_new_sit.COMPOSITE_SCORE
        WHERE CS_SECOND_SCALE IN (SELECT DD_DIMID

                                  FROM morse_new_sit.DIM_DEF

                                  WHERE DD_DIMTYPE = ip_type

                                  AND DD_DIMSUBTYPE = ip_frqsubype

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

                                  AND DD_STATUS = 0)

                        AND CS_FIRST_SCALE IN (SELECT DD_DIMID

                                  FROM morse_new_sit.DIM_DEF

                                  WHERE DD_DIMTYPE = ip_type

                                  AND DD_DIMSUBTYPE = ip_svrsubType

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

                                  AND DD_STATUS = 0)

        AND CS_STATUS = 0
        LOOP
        OP_CLRLIST_array := array_append(OP_CLRLIST_array,OP_CLRLIST);
        RAISE NOTICE 'OP_CLRLIST %',OP_CLRLIST;
*        proc_get_freq_svrty_array1.OP_CLRLIST = OP_CLRLIST_array ;*
 --RETURN OP_CLRLIST_array;
    END LOOP;
    m := array_length(OP_CLRLIST_array, 1);
RAISE NOTICE ' array count ::: %',m;
--RETURN  anyarray;

END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;

*I am getting null in the output *
*/
Appreciate your help merlin /*

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