Thread: Dumping rows into an array?

Hi

I'm new to Postgres, but I've been having fun with it.  In our
application we want to be able to store a variable number of
keywords for a record.

I first thought that an array column would be the way to go, but
after reading caveats on performance, I implemented they keywords
as a separate table.  This works great, but now I have another
array question.

I want to pass the keywords for the record as an array to a stored
procedure (PL/pqsql), for inserting, and also return them as an array.

I got the input part working like this

   SELECT INTO len array_upper(kw, 1);
   FOR idx IN 1 .. len LOOP
      INSERT INTO keywords
         VALUES(DEFAULT, lid, kw[idx]);
   END LOOP;

kw is a TEXT[] parameter to the fuction.

I've been unable to come up with the counterpart to select the
keywords and populate an array that I can return..

If I do

   DECLARE kwlist TEXT[];
...

   SELECT ARRAY[keyword] INTO keywordlist


I get one keyword, cast as an array.

I've tried

   DECLARE kwlist TEXT[];
   DECLARE kw RECORD;
...


    FOR kw IN SELECT keyword FROM keywords as kk
        WHERE lesson.id = kk.id
        ORDER BY id
        LOOP
          kwary[idx] := kw;
          idx := idx + 1;
        END LOOP;

But only one word gets returned in kwary[].  At least that's what I
get after doing

    lesson.keywordlist := kwary

Where lesson is the record I return from the function, and
keywordlist is a member of the record of type TEXT[].

Any ideas on how I might accomplish this?  I"m open to any
suggestions, including using a different way of passing the
keywords.  The only requirement I have is that I must be able to
pass in a variable length list of words, which I seem to have
solved, and that I need to be able to return a variable length list
fo words, which is driving me crazy.

Thanks for any pointers,

-karl

PS, if it makes a difference, the application is  using Perl and
DBD::Pg to query the database.  I see the same results when I call
the functions from psql.

karly@kipshouse.org writes:
> I've been unable to come up with the counterpart to select the
> keywords and populate an array that I can return..

I think you want something along the lines of

    kwlist := array(select keyword from keywords where ...);

            regards, tom lane

On Wed, Mar 15, 2006 at 09:52:48AM -0500, Tom Lane wrote:
> karly@kipshouse.org writes:
> > I've been unable to come up with the counterpart to select the
> > keywords and populate an array that I can return..
>
> I think you want something along the lines of
>
>     kwlist := array(select keyword from keywords where ...);
>
>             regards, tom lane

Thanks for the reply.

I thought I had tried this, and I had tried something similar, but
for some reason I was using the syntax

   ARRAY[(SELECT ...)]

which didn't work.  I'll have to go read Section 8.10 again.

Thanks for the help.  Now on to my next bug.  {-;

-karl