Returning rowsets/results from a function to another one in plpgsl.

[Mr sidh bhatt]

hi all,

I have a little strange scenario i need a function A
to call function B where in function B fetches a set
of records and returns the set to function A.

This is what i have going

CREATE OR REPLACE FUNCTION test(VARCHAR (40)) RETURNS
int AS
'
DECLARE
a_status_name ALIAS FOR $1;
count int;
arrStore text[];
BEGIN
count := 1;
SELECT INTO arrStore return_to_me(CAST (a_status_name
AS VARCHAR)) ;
WHILE arrStore[count] LOOP
RAISE NOTICE ''Print element %.'',arrStore[count];
count := count + 1;
END LOOP;
RETURN 1 ;
END
'
LANGUAGE 'plpgsql';
where return_to_me is as below:

CREATE OR REPLACE FUNCTION
return_to_me
(
VARCHAR(40)
)
RETURNS text[] AS
'
DECLARE
a_status_name ALIAS FOR $1;
order_rec record;
record_data text;
return_arr text[];
counter int;
BEGIN
counter := 1;
FOR order_rec in
SELECT ..
FROM ..
WHERE ..
LOOP
IF counter = 1 THEN
record_data = order_rec.ord_proc_state_guid;
ELSE
record_data = record_data || '','' || order_rec.field;
END IF;
counter := counter + 1;
END LOOP;

return_arr := ''{'' || record_data || ''}'';

RETURN return_arr;
END
'
LANGUAGE 'plpgsql';

versions of postgres below 7.4 ( i use 7.3.4) do not
support arrays as real arrays but as strings
{'a','b','bb'} and thats why i created the string as
above, the return_to_me function generates the psuedo
array correctly as {Sam,Miller,25} but when this needs
to be returned to the callee function test I get the
error

ERROR: syntax error at or near "[".

Am i doing something wrong (i guess so) If there is an
alternate way to achieve the same please do reply .
All help be greatly appreciated.

thanks
Sid

__________________________________________________ ______________________
Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html