SELECT INTO Array?

On Fri, Apr 01, 2005 at 03:22:58PM +1200, Zitan Broth wrote:

I was wondering if there was an easy way of converting the output
from a SELECT statement into an Array ..... I'd like to be able to
SELECT INTO MyArray[] * FROM TABLE WHERE ID=1 ..... is this possible?

In 7.4 and later you can use an array constructor with a subquery
that selects a single column:

CREATE TABLE foo (
id integer PRIMARY KEY,
name text NOT NULL
);

INSERT INTO foo (id, name) VALUES (1, 'John');
INSERT INTO foo (id, name) VALUES (2, 'David');
INSERT INTO foo (id, name) VALUES (3, 'James');

SELECT ARRAY(SELECT id FROM foo WHERE name LIKE 'J%');
?column?
----------
{1,3}
(1 row)

Here's a PL/pgSQL example:

DECLARE
a integer[] := ARRAY(SELECT id FROM foo WHERE name LIKE 'J%');

Selecting multiple columns into an array doesn't work in SQL or
PL/pgSQL -- that could cause problems in the general case because
columns might have different types and arrays contain elements of
the same type. However, some other languages' interfaces to
PostgreSQL can return rows as arrays (e.g., Perl DBI).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,

Thanks a lot for your response.

Basically I am selecting out "one row" from the database with multiple
columns (should have told you that earlier) so it seems that unfortunately
your elegant approach below might not work. Although some of the columns
are text and some numeric I was planning to load them all into strings.
Perhaps I could convert the output to strings on the fly or something?

Otherwise I guess I could select the data into a temporary table and then
loop through each column adding the value to my array - will search around
for this.

Can you tell I'm normally a php developer? :-)

Thanks again,
Z.

----- Original Message ----- > On Fri, Apr 01, 2005 at 03:22:58PM +1200,
Zitan Broth wrote:

On Sat, Apr 02, 2005 at 12:29:15PM +1200, Zitan Broth wrote:

Basically I am selecting out "one row" from the database with multiple
columns (should have told you that earlier) so it seems that unfortunately
your elegant approach below might not work. Although some of the columns
are text and some numeric I was planning to load them all into strings.
Perhaps I could convert the output to strings on the fly or something?

What are you trying to do? That is, what's the ultimate purpose
of building the array? It might be easier to make suggestions if
we knew the "what" rather than focusing on a particular "how."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,

Good question :-)

I am writing a function that has a row of data passed into it as variables
and an ID. The function needs to pull out the appropriate row of data and
compare each data item which those passed in. For each found difference a
log row is written somewhere else and if there are *any* differences the
whole row is updated.

I was building an array from the passed in parameters with ||, then I wanted
to build an array from a query (with SELECT INTO) and then compare each
value with a loop:

FOR i IN 1..17 LOOP
-- compare current with new data
if CurrentData[i] is not NewData[i] then
ChangeFound := true;
-- load change into log table

Make sense? Thanks again,

G.

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Zitan Broth" <zitan@mediasculpt.net>
Cc: <pgsql-general@postgresql.org>
Sent: Saturday, April 02, 2005 12:57 PM
Subject: Re: [GENERAL] SELECT INTO Array?

On Sat, Apr 02, 2005 at 12:29:15PM +1200, Zitan Broth wrote:

Basically I am selecting out "one row" from the database with multiple
columns (should have told you that earlier) so it seems that

unfortunately

your elegant approach below might not work. Although some of the

columns

----- Original Message -----
From: "Zitan Broth" <zitan@mediasculpt.net>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, April 01, 2005 9:00 PM
Subject: Re: [GENERAL] SELECT INTO Array?

Hi Michael,

Good question :-)

I am writing a function that has a row of data passed into it as variables
and an ID. The function needs to pull out the appropriate row of data and
compare each data item which those passed in. For each found difference a
log row is written somewhere else and if there are *any* differences the
whole row is updated.

I was building an array from the passed in parameters with ||, then I
wanted
to build an array from a query (with SELECT INTO) and then compare each
value with a loop:

FOR i IN 1..17 LOOP
-- compare current with new data
if CurrentData[i] is not NewData[i] then
ChangeFound := true;
-- load change into log table

Make sense? Thanks again,

I think other folks hinted at this before, but you might consider using
pl/perl or pl/python for this. Array support and manipulation is more
advanced in these languages than in pl/pgsql, at least for manipulation
within the function.