Thread: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
I am doing Oracle to PostgreSQL migration activity as part of Procedure
Migration in Oracle there are *OUT parameters which return records(using
bulk collect) of custom type.*

*like function returing type1,type2. *

What will be alternative for PostgreSQL to do this.

*There are OUT parameters in PostgreSQL but i am not able to set returns set
of type1,type2 .
*
Appreciate your Help.

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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Thu, Jun 14, 2012 at 1:10 AM, utsav <utsav.pshah@tcs.com> wrote:
> I am doing Oracle to PostgreSQL migration activity as part of Procedure
> Migration in Oracle there are *OUT parameters which return records(using
> bulk collect) of custom type.*
>
> *like function returing type1,type2. *
>
> What will be alternative for PostgreSQL to do this.
>
> *There are OUT parameters in PostgreSQL but i am not able to set returns set
> of type1,type2 .
> *
> Appreciate your Help.

postgres=# create type foo as (a int, b text);
CREATE TYPE
postgres=# create type bar as (c int, d text);
CREATE TYPE
postgres=# create function f(foo out foo, bar out bar) returns setof
record as $$
  select (v, v::text)::foo, (v, v::text)::bar from generate_series(1,3) v;
$$ language sql;
CREATE FUNCTION
postgres=# select f();
         f
-------------------
 ("(1,1)","(1,1)")
 ("(2,2)","(2,2)")
 ("(3,3)","(3,3)")
(3 rows)

postgres=# select * from f();
  foo  |  bar
-------+-------
 (1,1) | (1,1)
 (2,2) | (2,2)
 (3,3) | (3,3)
(3 rows)

postgres=# select (foo).*, (bar).* from f();
 a | b | c | d
---+---+---+---
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
 3 | 3 | 3 | 3
(3 rows)

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
Thanks for reply but you have used SQL as a language .

Please give me Plpgsql example because  i am facing problem in plpgsql only
..


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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Mon, Jun 18, 2012 at 4:30 AM, utsav <utsav.pshah@tcs.com> wrote:
> Thanks for reply but you have used SQL as a language .
>
> Please give me Plpgsql example because  i am facing problem in plpgsql only

create function f(foo out foo, bar out bar) returns setof
record as $$
begin
 return query select (v, v::text)::foo, (v, v::text)::bar from
generate_series(1,3) v;
end
$$ language plpgsql;

 -- or --

create or replace function f(foo out foo, bar out bar) returns setof
record as $$
begin
 f.foo = (1,'a')::foo;
 f.bar = (2,'b')::bar;
 return next;
end
$$ language plpgsql;

(in older versions of postgres you might have to be a little more
careful about names of input and output arguments).

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
-- Table: bar

-- DROP TABLE bar;

CREATE TABLE bar
(
  barid integer,
  barsubid integer,
  barname text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bar
  OWNER TO postgres;
--------------------------------------------------------------------------------------------
-- Table: foo

-- DROP TABLE foo;

CREATE TABLE foo
(
  fooid integer,
  foosubid integer,
  fooname text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE foo
  OWNER TO postgres;

--------------------------------------------------------------------------------------------


-- Function: getallfoobar()

-- DROP FUNCTION getallfoobar();

CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
     r foo%rowtype;
     r1 bar%rowtype;
 BEGIN
     FOR r IN SELECT * FROM foo
     WHERE fooid > 3
     LOOP
         -- can do some processing here
              RAISE NOTICE 'r == %',r;
         -- return next row of SELECT
     END LOOP;

     FOR r1 IN SELECT * FROM bar
     WHERE barid > 0
     LOOP
         -- can do some processing here
          -- return next row of SELECT
         RAISE NOTICE 'r1 == %',r1;
     END LOOP;
     RETURN NEXT;
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
-----------------------------------------------------------------------------------

select * from getallfoobar3();


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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
I am getting null in output.

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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Mon, Jun 18, 2012 at 12:04 PM, utsav <utsav.pshah@tcs.com> wrote:
> -- Table: bar
>
> -- DROP TABLE bar;
>
> CREATE TABLE bar
> (
>  barid integer,
>  barsubid integer,
>  barname text
> )
> WITH (
>  OIDS=FALSE
> );
> ALTER TABLE bar
>  OWNER TO postgres;
> --------------------------------------------------------------------------------------------
> -- Table: foo
>
> -- DROP TABLE foo;
>
> CREATE TABLE foo
> (
>  fooid integer,
>  foosubid integer,
>  fooname text
> )
> WITH (
>  OIDS=FALSE
> );
> ALTER TABLE foo
>  OWNER TO postgres;
>
> --------------------------------------------------------------------------------------------
>
>
> -- Function: getallfoobar()
>
> -- DROP FUNCTION getallfoobar();
>
> CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
>  RETURNS SETOF record AS
> $BODY$
>  DECLARE
>     r foo%rowtype;
>     r1 bar%rowtype;
>  BEGIN
>     FOR r IN SELECT * FROM foo
>     WHERE fooid > 3
>     LOOP
>         -- can do some processing here
>              RAISE NOTICE 'r == %',r;
>         -- return next row of SELECT
>     END LOOP;
>
>     FOR r1 IN SELECT * FROM bar
>     WHERE barid > 0
>     LOOP
>         -- can do some processing here
>          -- return next row of SELECT
>         RAISE NOTICE 'r1 == %',r1;
>     END LOOP;
>     RETURN NEXT;
>  END
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100
>  ROWS 1000;
> -----------------------------------------------------------------------------------
>
> select * from getallfoobar3();

you're getting null results because you never assigned anything to
your output variables.  'RETURN NEXT' will emit a new record for both
OUT foo and OUT bar based on whatever they are containing at the time.
 Try running my example above and extending it.

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
-- Function: getallfoobar()

-- DROP FUNCTION getallfoobar();

CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
     r foo%rowtype;
     r1 bar%rowtype;

BEGIN
     FOR r IN SELECT * FROM foo
     WHERE fooid > 3
     LOOP
         -- can do some processing here
              RAISE NOTICE 'r == %',r;
         -- return next row of SELECT'
         getallfoobar3.foo = r;
     END LOOP;

     FOR r1 IN SELECT * FROM bar
     WHERE barid > 0
     LOOP
         -- can do some processing here
          -- return next row of SELECT
         RAISE NOTICE 'r1 == %',r1;
     END LOOP;
    getallfoobar3.bar = r1;
     RETURN NEXT;
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;


/Thanks for your help ../

*But still i want output in record here i am getting only last record in
ouput  ...*

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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Mon, Jun 18, 2012 at 1:33 PM, utsav <utsav.pshah@tcs.com> wrote:
> -- Function: getallfoobar()
>
> -- DROP FUNCTION getallfoobar();
>
> CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
>  RETURNS SETOF record AS
> $BODY$
>  DECLARE
>     r foo%rowtype;
>     r1 bar%rowtype;
>
> BEGIN
>     FOR r IN SELECT * FROM foo
>     WHERE fooid > 3
>     LOOP
>         -- can do some processing here
>              RAISE NOTICE 'r == %',r;
>         -- return next row of SELECT'
>         getallfoobar3.foo = r;
>     END LOOP;
>
>     FOR r1 IN SELECT * FROM bar
>     WHERE barid > 0
>     LOOP
>         -- can do some processing here
>          -- return next row of SELECT
>         RAISE NOTICE 'r1 == %',r1;
>     END LOOP;
>    getallfoobar3.bar = r1;
>     RETURN NEXT;
>  END
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100
>  ROWS 1000;
>
>
> /Thanks for your help ../
>
> *But still i want output in record here i am getting only last record in
> ouput  ...*

sure -- you're only calling one 'return next'.  you need to call
return next for each row you want to return.

you've also got two loops -- that isn't going to work as intended.
your code should be structured like this:

FOR <something that gets same sized list of foo and bar>
LOOP
  <get a foo into f>
  foo := f;
  <get a bar into b>
  bar := b;
  RETURN NEXT;
END LOOP;

If you want heterogeneously sized lists to be returned from a single
function, you might want to consider returning arrays, not a set
returning function.

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
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.

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
Ya but when i use the return next it gives me all OUT parameters but i will
get last record of out parameter1 repetitive untill the last record of last
out parameter . Sorry i didn't have output with me . Is there any other way
to achive this ?

Many Thanks for your help merlin ...

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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Wed, Jun 20, 2012 at 12:15 PM, utsav <utsav.pshah@tcs.com> wrote:
> Ya but when i use the return next it gives me all OUT parameters but i will
> get last record of out parameter1 repetitive untill the last record of last
> out parameter . Sorry i didn't have output with me . Is there any other way
> to achive this ?
>
> Many Thanks for your help merlin ...

right -- exactly.  this is how set returning functions work.  Each
returned row contains both OUT variables.  You can't return a set of
parameter1 then swing around and return a set of parmameter2.  If you
want to return two independent sets, you are using the wrong
mechanism.

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
merlin can u please suggest any solution for achiving this . I have tried
array but still i am not getting how to return different array and for
returning array i must have to pass one array in input parameter and that
only i can return that is where i got stuck .

Thanks

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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
anyone had faced the same issue than pl help..

merlin : i am waiting for your reply ...


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

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
Merlin Moncure
Date:
On Wed, Jun 20, 2012 at 9:52 PM, utsav <utsav.pshah@tcs.com> wrote:
> merlin can u please suggest any solution for achiving this . I have tried
> array but still i am not getting how to return different array and for
> returning array i must have to pass one array in input parameter and that
> only i can return that is where i got stuck .

why not make two set returning functions?   the array approach is
useful but may be difficult to use in your case.  it is awkward (read:
slow) to construct arrays iteratively and client processing can be
tricky depending on your application stack.

merlin

Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

From
utsav
Date:
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.