Обсуждение: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION
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.
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
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.
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
-- 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.
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.
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
-- 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.
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
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.
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
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.
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
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.
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.
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
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.