Re: Passing array to PL/SQL and looping

Is it possible to construct and return an array with plpgsql <br /><br /> like..<br /><br /> CREATE FUNCTION
test_array() RETURNS VARCHAR[] AS '<br /> DECLARE <br />     return_array VARCHAR[];<br /> BEGIN<br />    
return_array[0]:= ''test'';<br />     return_array[1] := ''test 1'';<br />     return_array[2] := ''test 2'';<br />
RETURN(return_array);<br /> END;'<br /> LANGUAGE 'plpgsql';<br /><br /> I get the following error when I try to run
it:<br/> ERROR:  parse error at or near "[" on line 4.<br /><br /> If this worked I could clean up a LOT of hacky
plpgsqlcode I have had to write.<br /><br /> On Thu, 2002-09-26 at 18:20, Josh Berkus wrote: <blockquote
type="CITE"><pre><fontcolor="#969d9d" size="3"><i>Peter,
 

> I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of
> id's to the function and then loop through until the array is empty. I know
> there must be atleast five things I'm doing wrong.

Simplified example:

CREATE FUNCTION test_array (INT[] ) 
RETURNS INT AS '
DECLARE id_array ALIAS for $1;count_it INT;
BEGIN
count_it := 1;
WHILE id_array[count_it] LOOPcount_it := count_it + 1;
END LOOP;
RETURN (count_it - 1);
END;'
LANGUAGE 'plpgsql';

returns the number of elements in the supplied array.

-- 
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to </i></font><a
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a><fontcolor="#969d9d"
size="3">)</font></pre></blockquote><tablecellpadding="0" cellspacing="0" width="100%"><tr><td> -- <br /> Greg Johnson
<<ahref="mailto:gregj@interprose.com">gregj@interprose.com</a>> </td></tr></table>