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

From: Mr sidh bhatt <shb123in(at)yahoo(dot)co(dot)in>
To: pgsql-general(at)postgresql(dot)org
Subject: Returning rowsets/results from a function to another one in plpgsl.
Date: 2004-06-10 16:45:12
Message-ID: 20040610164512.29189.qmail@web8006.mail.in.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2004-06-10 16:47:32 Re: FK issue
Previous Message Dave Cramer 2004-06-10 16:36:02 Re: [JDBC] Trouble with locking tables - reg.