Thread: working with arrays inside a plpgsql function

working with arrays inside a plpgsql function

From

"Luis Silva"

Date:

15 February 2007, 13:08:24

Hi all!

I'm trying to use array inside a pl/pgsql function and I'm having some
problems using the all array at the same time. Let me give you an example:

ident text[];

BEGIN

ident = $1;  -- $1 is a array argument from the plpgsql function
EXECUTE 'SELECT count(identity) FROM public_data WHERE identity IN ||ident
INTO asds;


This last query returns an error. Any guess to solve this problem? tks a lot
PS - How can I retrieved the size ofthe array? I saw the manual and I
couldn't find it.

Regards,

Lu�s

_________________________________________________________________
Check out all that glitters with the MSN Entertainment Guide to the Academy
Awards�   http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline2

Re: working with arrays inside a plpgsql function

From

Andreas Kretschmer

Date:

15 February 2007, 15:07:04

Luis Silva <lfs12@hotmail.com> schrieb:

> Hi all!
>
> I'm trying to use array inside a pl/pgsql function and I'm having some
> problems using the all array at the same time. Let me give you an example:
>
> ident text[];
>
> BEGIN
>
> ident = $1;  -- $1 is a array argument from the plpgsql function
> EXECUTE 'SELECT count(identity) FROM public_data WHERE identity IN ||ident
> INTO asds;

At least a missing ' between IN and ||.


> PS - How can I retrieved the size ofthe array? I saw the manual and I
> couldn't find it.

array_dims(), array_lower(), array_upper()
http://www.postgresql.org/docs/8.1/interactive/functions-array.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: working with arrays inside a plpgsql function

From

"Luis Silva"

Date:

15 February 2007, 16:17:05

Tks for the answer, I used a different approach based on the information
that you supplied:

Used the function array_to_string and passed the info to a text variable.

BTW, is it possible to pass into an array the result of a query? tks again,
the last answer was very useful!

>From: Andreas Kretschmer <akretschmer@spamfence.net>
>To: pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] working with arrays inside a plpgsql function
>Date: Thu, 15 Feb 2007 17:06:56 +0100
>
>Luis Silva <lfs12@hotmail.com> schrieb:
>
> > Hi all!
> >
> > I'm trying to use array inside a pl/pgsql function and I'm having some
> > problems using the all array at the same time. Let me give you an
>example:
> >
> > ident text[];
> >
> > BEGIN
> >
> > ident = $1;  -- $1 is a array argument from the plpgsql function
> > EXECUTE 'SELECT count(identity) FROM public_data WHERE identity IN
>||ident
> > INTO asds;
>
>At least a missing ' between IN and ||.
>
>
> > PS - How can I retrieved the size ofthe array? I saw the manual and I
> > couldn't find it.
>
>array_dims(), array_lower(), array_upper()
>http://www.postgresql.org/docs/8.1/interactive/functions-array.html
>
>
>Andreas
>--
>Really, I'm not out to destroy Microsoft. That will just be a completely
>unintentional side effect.                              (Linus Torvalds)
>"If I was god, I would recompile penguin with --enable-fly."    (unknow)
>Kaufbach, Saxony, Germany, Europe.              N 51.05082�, E 13.56889�
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster

_________________________________________________________________
Valentine�s Day -- Shop for gifts that spell L-O-V-E at MSN Shopping
http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptnrdata=24095&tcode=wlmtagline