Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DC76347618E
	for <pgsql-sql@postgresql.org>; Fri, 27 Sep 2002 11:32:15 -0400 (EDT)
Received: from mail.interprose.com (ns.mthoodmedia.com [208.186.79.1])
	by postgresql.org (Postfix) with ESMTP id DAAFF4760EF
	for <pgsql-sql@postgresql.org>; Fri, 27 Sep 2002 11:32:14 -0400 (EDT)
Received: from watto.interprose.net (watto.interprose.net [192.168.1.164])
	by mail.interprose.com (Postfix) with ESMTP
	id AC51B2141F1; Fri, 27 Sep 2002 08:32:19 -0700 (PDT)
Subject: Re: Passing array to PL/SQL and looping
From: Greg Johnson <gregj@interprose.com>
To: Josh Berkus <josh@agliodbs.com>, pgsql-sql@postgresql.org
In-Reply-To: <200209261820.08106.josh@agliodbs.com>
References: <1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL>
	<200209261820.08106.josh@agliodbs.com>
Content-Type: multipart/alternative; boundary="=-posm/ngPmzy5NU6eUo8q"
Organization: 
Message-Id: <1033140741.4254.12.camel@watto.interprose.net>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.1.1 (Preview Release)
Date: 27 Sep 2002 08:32:21 -0700
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/394
X-Sequence-Number: 9671

--=-posm/ngPmzy5NU6eUo8q
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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@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 majordomo@postgresql.org)
> 
-- 
Greg Johnson <gregj@interprose.com>

--=-posm/ngPmzy5NU6eUo8q
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/1.1.2">
</HEAD>
<BODY>
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>
&nbsp;&nbsp;&nbsp; return_array VARCHAR[];<BR>
BEGIN<BR>
&nbsp;&nbsp;&nbsp; return_array[0] := ''test'';<BR>
&nbsp;&nbsp;&nbsp; return_array[1] := ''test 1'';<BR>
&nbsp;&nbsp;&nbsp; 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:&nbsp; parse error at or near &quot;[&quot; on line 4.<BR>
<BR>
If this worked I could clean up a LOT of hacky plpgsql code I have had to write.<BR>
<BR>
On Thu, 2002-09-26 at 18:20, Josh Berkus wrote:</FONT>
<BLOCKQUOTE TYPE=CITE>
<PRE><FONT COLOR="#969d9d" SIZE="3"><I>Peter,

&gt; I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of
&gt; id's to the function and then loop through until the array is empty. I know
&gt; 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@agliodbs.com
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
&nbsp;&nbsp;&nbsp; (send &quot;unregister YourEmailAddressHere&quot; to </FONT><A HREF="mailto:majordomo@postgresql.org">majordomo@postgresql.org</FONT></A><FONT COLOR="#969d9d" SIZE="3">)</I></FONT></PRE>
</BLOCKQUOTE>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
-- <BR>
Greg Johnson &lt;<A HREF="mailto:gregj@interprose.com">gregj@interprose.com</A>&gt;
</TD>
</TR>
</TABLE>

</BODY>
</HTML>

--=-posm/ngPmzy5NU6eUo8q--