Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

On Wed, Jun 20, 2012 at 5:31 AM, utsav <utsav.pshah@tcs.com> wrote:
> 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 /*

uh, you have no return statements. of course the output is null.  if
you are using loops, you *must* use return next.  Also each return
next will return *all* the OUT variables.

merlin