Thread: Passing array to PL/SQL and looping
All,
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.
Please help!
Cheers,
-p
Call to Procedure and Array:
$myArray = array(15, 6, 23);
select generateInvoice($myArray);
Procedure:
CREATE FUNCTION generateInvoice (VARRAY) RETURNS int4 AS '
DECLARE -- local variables temppk INT4; v_pids := $1;
v_countBINARY_INTEGER := 1; id INT4;
BEGIN SELECT INTO temppk nextval(''t_task_task_id_seq'');
LOOP IF v_pids.EXISTS(v_count) THEN
id := v_pids.NEXT(v_count);
UPDATE t_project SET task_id=temppk WHERE project_id=id;
v_count := v_count + 1;
ELSE EXIT; END IF; END LOOP;
-- Everything has passed, return id as pk RETURN temppk; END;
' LANGUAGE 'plpgsql';
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
Greg, > CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS ' > DECLARE > return_array VARCHAR[]; > BEGIN > return_array[0] := ''test''; > return_array[1] := ''test 1''; > return_array[2] := ''test 2''; > RETURN (return_array); > END;' > LANGUAGE 'plpgsql'; No, it's not possible to do the above. This is a flaw in the current implementation of PL/pgSQL that will not be resolved until we attract some new Postgres hackers who really care about upgrading PL/pgSQL. Currently, if you want to use an array, it has to be passed as a parameter, or come from an external table. You cannot declare an Array data type. Annoying, really. -Josh Berkus
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>
On Friday 27 September 2002 18:04, Josh Berkus wrote:
> Greg,
>
> > CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS '
> > DECLARE
> > return_array VARCHAR[];
> > BEGIN
> > return_array[0] := ''test'';
> > return_array[1] := ''test 1'';
> > return_array[2] := ''test 2'';
> > RETURN (return_array);
> > END;'
> > LANGUAGE 'plpgsql';
>
> No, it's not possible to do the above. This is a flaw in the current
> implementation of PL/pgSQL that will not be resolved until we attract
> some new Postgres hackers who really care about upgrading PL/pgSQL.
>
> Currently, if you want to use an array, it has to be passed as a
> parameter, or come from an external table. You cannot declare an
> Array data type. Annoying, really.
If I replace the return_array allocations in the above example with this
line:
return_array := ''{ ''''test'''', ''''test 1'''', ''''test 2''''}'';
it _seems_ to work as expected, at least in 7.3b1., e.g.
test=> select array_dims(test_array) from test_array();array_dims
------------[1:3]
(1 row)
Ian Barwick
barwick@gmx.net
>>>>> "Greg" == Greg Johnson <gregj@interprose.com> writes:
Greg> CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS ' Greg> DECLARE Greg> return_array VARCHAR[];
Greg>BEGIN Greg> return_array[0] := ''test''; Greg> return_array[1] := ''test 1''; Greg>
return_array[2]:= ''test 2''; Greg> RETURN (return_array); Greg> END;' Greg> LANGUAGE 'plpgsql';
Greg> I get the following error when I try to run it: Greg> ERROR: parse error at or near "[" on line 4.
I raised this issue when dealing with version 7.1 and the conclusion
was that PL/PgSQL doesn't understand array syntax. If you construct
the array as { val, val, val, ... } it works, but that is not a
practical solution for most uses.
What can we do to at least get this on the radar screen as a known
bug?
roland
-- PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Ian Barwick <barwick@gmx.net> writes:
> On Friday 27 September 2002 18:04, Josh Berkus wrote:
>> Currently, if you want to use an array, it has to be passed as a
>> parameter, or come from an external table. You cannot declare an
>> Array data type. Annoying, really.
> If I replace the return_array allocations in the above example with this
> line:
> return_array := ''{ ''''test'''', ''''test 1'''', ''''test 2''''}'';
> it _seems_ to work as expected, at least in 7.3b1.
Yes, operations that work on whole-array values work fine in plpgsql.
It's accesses to array elements that aren't supported well. The main
case that seems to be missing in current sources is exactly assignment
to an array element, ie foo[n] := something.
More generally --- and this is really a shortcoming in our SQL language,
not specifically in plpgsql --- there isn't any way to construct an
array value as an expression result. I can imagine writing something
likeSELECT CAST(1,2,3 AS int[3]);
but we've got nothing like that at the moment. (If you don't see the
point of this as compared toSELECT CAST('{1,2,3}' AS int[3]);
then think about replacing the 1, 2, and 3 by arbitrary integer
expressions.)
regards, tom lane
Roland Roberts <roland@astrofoto.org> writes:
> What can we do to at least get this on the radar screen as a known
> bug?
Oh, it's on the radar screen all right. Who wants to step up and fix
it?
regards, tom lane
Tom Lane wrote: > Roland Roberts <roland@astrofoto.org> writes: > > What can we do to at least get this on the radar screen as a known > > bug? > > Oh, it's on the radar screen all right. Who wants to step up and fix > it? Do we need a TODO for it? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073