From ken@sunward.org Thu Dec  29 17:46:55 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 31EDE9DC996
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 13:46:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63774-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 13:46:54 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from anu.ie (mail.anu.net [213.247.49.236])
	by postgresql.org (Postfix) with ESMTP id 650309DC840
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 13:46:51 -0400 (AST)
Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet
	Mail Server X 3.2.8b1) for <pgsql-sql@postgresql.org>;
	Thu, 29 Dec 2005 17:46:50 +0000
From: "Ken Winter" <ken@sunward.org>
To: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
Subject: Arrays in PL/pgSQL routines?
Date: Thu, 29 Dec 2005 12:46:28 -0500
Message-ID: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0052_01C60C75.E6DFEE60"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.046 required=5 tests=[AWL=0.045,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.046
X-Spam-Level: 
X-Archive-Number: 200512/240
X-Sequence-Number: 23782

This is a multi-part message in MIME format.

------=_NextPart_000_0052_01C60C75.E6DFEE60
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Can arrays be declared in PL/pgSQL routines?  If so, how?

=20

Section 8.10 of the documentation
(http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to =
declare
and use arrays as table columns.  But I don't find any part of the
documentation that says how to declare a simple array local to a =
PL/pgSQL
function.  I tried the following guess, but it only won me a "syntax =
error
at or near VARCHAR:

=20

DECLARE

  my_array VARCHAR [];

  .

=20

~ TIA

~ Ken


------=_NextPart_000_0052_01C60C75.E6DFEE60
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Verdana;
	color:windowtext;
	font-weight:normal;
	font-style:normal;
	text-decoration:none none;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>Can arrays be declared in PL/pgSQL routines?&nbsp; =
If so,
how?</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>Section 8.10 of the documentation (<a
href=3D"http://www.postgresql.org/docs/7.4/static/arrays.html">http://www=
.postgresql.org/docs/7.4/static/arrays.html</a>)
tells how to declare and use arrays as table columns.&nbsp; But I =
don&#8217;t
find any part of the documentation that says how to declare a simple =
array
local to a PL/pgSQL function.&nbsp; I tried the following guess, but it =
only
won me a &#8220;syntax error at or near VARCHAR:</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>DECLARE</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp; my_array VARCHAR [];</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp; &#8230;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ TIA</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ Ken</span></font></p>

</div>

</body>

</html>

------=_NextPart_000_0052_01C60C75.E6DFEE60--





From banderson@presinet.com Thu Dec  29 17:52:40 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5122B9DC843
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 13:52:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 66530-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 13:52:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from presinet.com (presinet.com [209.53.156.1])
	by postgresql.org (Postfix) with ESMTP id 740379DC817
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 13:52:37 -0400 (AST)
Received: from [10.10.1.151] ([10.10.1.151]) by presinet.com with Microsoft
	SMTPSVC(6.0.3790.1830); Thu, 29 Dec 2005 09:49:36 -0800
Message-ID: <43B42273.9030602@presinet.com>
Date: Thu, 29 Dec 2005 09:52:51 -0800
From: Bricklen Anderson <banderson@presinet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Ken Winter <ken@sunward.org>
CC: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines?
References: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
In-Reply-To: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 29 Dec 2005 17:49:36.0406 (UTC)
	FILETIME=[3BCA6B60:01C60CA0]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/242
X-Sequence-Number: 23784

Ken Winter wrote:
> Can arrays be declared in PL/pgSQL routines?  If so, how?
>  
<snip>
> DECLARE
> 
try:
my_array VARCHAR[] := '{}';

not sure if this works in 7.4 though, if that's the version that you are 
using.



From mike@fuhr.org Thu Dec  29 18:21:28 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 8F9229DC833
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:21:28 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 73804-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:21:25 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id F1DA69DC827
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 14:21:23 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jBTILL5T008265
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 29 Dec 2005 11:21:23 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jBTILL99053506;
	Thu, 29 Dec 2005 11:21:21 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jBTILL9Y053505;
	Thu, 29 Dec 2005 11:21:21 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 29 Dec 2005 11:21:21 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Winter <ken@sunward.org>
Cc: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines?
Message-ID: <20051229182120.GA53437@winnie.fuhr.org>
References: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <005101c60c9f$cfb5f660$6603a8c0@kenxp>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.055 required=5 tests=[AWL=0.055]
X-Spam-Score: 0.055
X-Spam-Level: 
X-Archive-Number: 200512/243
X-Sequence-Number: 23785

On Thu, Dec 29, 2005 at 12:46:28PM -0500, Ken Winter wrote:
> Can arrays be declared in PL/pgSQL routines?  If so, how?
> 
> Section 8.10 of the documentation
> (http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to declare
> and use arrays as table columns.  But I don't find any part of the
> documentation that says how to declare a simple array local to a PL/pgSQL
> function.  I tried the following guess, but it only won me a "syntax error
> at or near VARCHAR:
> 
> DECLARE
> 
>   my_array VARCHAR [];

What version of PostgreSQL are you using?  Could you post a complete
function instead of just an excerpt?  The following works for me in
7.4.10 and later but not in 7.3.12:

CREATE FUNCTION foo(varchar, varchar, varchar) RETURNS varchar[] AS '
DECLARE
    my_array varchar[] := ''{}'';
BEGIN
    my_array[1] := $1;
    my_array[2] := $2;
    my_array[3] := $3;
    RETURN my_array;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT foo('a', 'b', 'c');
   foo   
---------
 {a,b,c}
(1 row)

Array handling was improved in 7.4; in earlier versions you'll
probably get an error like the following after SELECT:

WARNING:  plpgsql: ERROR during compile of foo near line 4
ERROR:  syntax error at or near "["

That's a little different than your "syntax error at or near VARCHAR."
Was that the actual error message?

-- 
Michael Fuhr



From ken@sunward.org Thu Dec  29 18:23:38 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 091D69DC833
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:23:38 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 72586-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:23:37 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from anu.ie (mail.anu.net [213.247.49.236])
	by postgresql.org (Postfix) with ESMTP id 7E9569DC840
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 14:23:35 -0400 (AST)
Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet
	Mail Server X 3.2.8b1); Thu, 29 Dec 2005 18:23:35 +0000
From: "Ken Winter" <ken@sunward.org>
To: "'Bricklen Anderson'" <banderson@presinet.com>
Cc: "'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines?
Date: Thu, 29 Dec 2005 13:23:28 -0500
Message-ID: <005a01c60ca4$f79e3bb0$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
In-Reply-To: <43B42273.9030602@presinet.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.053 required=5 tests=[AWL=0.053]
X-Spam-Score: 0.053
X-Spam-Level: 
X-Archive-Number: 200512/244
X-Sequence-Number: 23786

Bricklen ~

That works.  (Odd that the initialization seems to be necessary to make it
work.)  Thanks!  Yes, I'm using version 7.4.

~ Ken

> -----Original Message-----
> From: Bricklen Anderson [mailto:banderson@presinet.com]
> Sent: Thursday, December 29, 2005 12:53 PM
> To: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Arrays in PL/pgSQL routines?
> 
> Ken Winter wrote:
> > Can arrays be declared in PL/pgSQL routines?  If so, how?
> >
> <snip>
> > DECLARE
> >
> try:
> my_array VARCHAR[] := '{}';
> 
> not sure if this works in 7.4 though, if that's the version that you are
> using.





From tgl@sss.pgh.pa.us Thu Dec  29 18:42:21 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 72EDC9DC833
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:42:21 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76679-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:42:20 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 9749B9DC827
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 14:42:18 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBTIgIuv022480;
	Thu, 29 Dec 2005 13:42:18 -0500 (EST)
To: "Ken Winter" <ken@sunward.org>
cc: "'Bricklen Anderson'" <banderson@presinet.com>,
	"'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines? 
In-reply-to: <005a01c60ca4$f79e3bb0$6603a8c0@kenxp> 
References: <005a01c60ca4$f79e3bb0$6603a8c0@kenxp>
Comments: In-reply-to "Ken Winter" <ken@sunward.org>
	message dated "Thu, 29 Dec 2005 13:23:28 -0500"
Date: Thu, 29 Dec 2005 13:42:18 -0500
Message-ID: <22479.1135881738@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.053 required=5 tests=[AWL=0.053]
X-Spam-Score: 0.053
X-Spam-Level: 
X-Archive-Number: 200512/245
X-Sequence-Number: 23787

"Ken Winter" <ken@sunward.org> writes:
> That works.  (Odd that the initialization seems to be necessary to make it
> work.)  Thanks!  Yes, I'm using version 7.4.

Possibly what you're running into is that 7.4 is restrictive about what
you can do with an array that's NULL.  Until pretty recently, assigning
to an element of a NULL array left the array still NULL --- this is an
artifact of the fact that the system sees the assignment as a binary
operation with the array and the new element as inputs.

			regards, tom lane



From mike@fuhr.org Thu Dec  29 18:46:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id A23E99DC840
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:46:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76742-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 14:46:44 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id 26B309DC827
	for <pgsql-sql@postgresql.org>; Thu, 29 Dec 2005 14:46:41 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jBTIkdJL008291
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 29 Dec 2005 11:46:41 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jBTIkdYd053756;
	Thu, 29 Dec 2005 11:46:39 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jBTIkda9053755;
	Thu, 29 Dec 2005 11:46:39 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 29 Dec 2005 11:46:39 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Winter <ken@sunward.org>
Cc: "'Bricklen Anderson'" <banderson@presinet.com>,
	"'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Arrays in PL/pgSQL routines?
Message-ID: <20051229184639.GA53686@winnie.fuhr.org>
References: <43B42273.9030602@presinet.com>
	<005a01c60ca4$f79e3bb0$6603a8c0@kenxp>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <005a01c60ca4$f79e3bb0$6603a8c0@kenxp>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.055 required=5 tests=[AWL=0.055]
X-Spam-Score: 0.055
X-Spam-Level: 
X-Archive-Number: 200512/246
X-Sequence-Number: 23788

On Thu, Dec 29, 2005 at 01:23:28PM -0500, Ken Winter wrote:
> That works.  (Odd that the initialization seems to be necessary to make it
> work.)  Thanks!  Yes, I'm using version 7.4.

7.4.what?  Absence of initialization shouldn't cause a syntax error;
at least it doesn't in 7.4.10.  However, in 7.4 you do have to
initialize an array before assigning to its elements, else the array
will remain NULL (initialization isn't necessary in the latest 8.x
releases).

-- 
Michael Fuhr