Thread: Acccessing individual array elements form plpgsql

Hi:

plpgsql allows me to define an array:

 direction TEXT[2]:=array['out','in'];

and I can print out the whole array via:

 RAISE NOTICE 'direction=%',direction;

getting:

 direction={in,out}

but how do I access members of this array individually??? I'd love it if
direction[1] would pull out the 2nd or 1st element so that I could use it in
the selects which my procedure is forming, but I get syntax errors when I try
to reference the array element in such a way. For example:

 RAISE NOTICE '%',direction[1];

gives:

 ERROR:  syntax error at or near "[" at character ####
 LINE ##: RAISE NOTICE '%',direction[1];
                                     ^
Thanks for any help which you can give. I am running postgresql 8.0.1.

Celia McInnis

--
Open WebMail Project (http://openwebmail.org)

"Celia McInnis" <celia@drmath.ca> writes:
> but how do I access members of this array individually??? I'd love it if
> direction[1] would pull out the 2nd or 1st element so that I could use it in
> the selects which my procedure is forming, but I get syntax errors when I try
> to reference the array element in such a way. For example:

>  RAISE NOTICE '%',direction[1];
>  ERROR:  syntax error at or near "[" at character ####

The RAISE statement is pretty limited: it won't take a general
expression as an argument, only a bare variable name.  But you should be
able to subscript direction in other contexts.  If you really need to
put out a notice using this value, assign it to a temporary variable ...

            regards, tom lane

AHAH!!! Thanks Tom! I had only tried the printing to figure out why a
complicated query command which I was generating was ending up as NULL, and
being a newbie, thought that there was something wrong with my array usage
rather than that RAISE NOTICE just couldn't deal with an indexed variable. I'm
now writing the array elements to scalar variables before printing them out.

Is there some better way to print out debugging info from a stored procedure?

Celia

On Fri, 11 Mar 2005 17:46:49 -0500, Tom Lane wrote
> "Celia McInnis" <celia@drmath.ca> writes:
> > but how do I access members of this array individually??? I'd love it if
> > direction[1] would pull out the 2nd or 1st element so that I could use it in
> > the selects which my procedure is forming, but I get syntax errors when I try
> > to reference the array element in such a way. For example:
>
> >  RAISE NOTICE '%',direction[1];
> >  ERROR:  syntax error at or near "[" at character ####
>
> The RAISE statement is pretty limited: it won't take a general
> expression as an argument, only a bare variable name.  But you
> should be able to subscript direction in other contexts.  If you
> really need to put out a notice using this value, assign it to a
> temporary variable ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


--
Open WebMail Project (http://openwebmail.org)

"Celia McInnis" <celia@drmath.ca> writes:
> Is there some better way to print out debugging info from a stored procedure?

Not really.  The RAISE statement needs some TLC, basically, but no one's
gotten around to it.  Aside from the prohibition on expressions, which
is just silly, it needs to support specifying an error code when
throwing errors ...

            regards, tom lane