From: | karly(at)kipshouse(dot)org |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dumping rows into an array? |
Date: | 2006-03-15 06:15:15 |
Message-ID: | 20060314221515.C18006@kipshouse.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | chris smith | 2006-03-15 07:13:09 | Re: out of memory using Postgres with Spring/Hibernate/Java |
Previous Message | maarten roosendaal | 2006-03-15 06:06:06 | out of memory using Postgres with Spring/Hibernate/Java |