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_count BINARY_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] LOOP
count_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(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco

Is it possible to construct and return an array with plpgsql

like..

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';

I get the following error when I try to run it:
ERROR: parse error at or near "[" on line 4.

If this worked I could clean up a LOT of hacky plpgsql code I have had
to write.

On Thu, 2002-09-26 at 18:20, Josh Berkus wrote:

> 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] LOOP
> count_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(at)agliodbs(dot)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 majordomo(at)postgresql(dot)org)
>
--
Greg Johnson <gregj(at)interprose(dot)com>

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

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(at)gmx(dot)net

>>>>> "Greg" == Greg Johnson <gregj(at)interprose(dot)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(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Ian Barwick <barwick(at)gmx(dot)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
like
SELECT 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 to
SELECT 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(at)astrofoto(dot)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(at)astrofoto(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073