Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT INTO Array?

SELECT INTO Array?

From
"Zitan Broth"
Date:
Greetings All,
 
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?  What is the best way of doing this?
 
Thanks - sorry if this is newbie, but I trolled and trolled .... lol
 
Z.

Re: SELECT INTO Array?

From
Michael Fuhr
Date:
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/

Re: SELECT INTO Array?

From
"Zitan Broth"
Date:
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:
> >
> > 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: SELECT INTO Array?

From
Michael Fuhr
Date:
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/

Re: SELECT INTO Array?

From
"Zitan Broth"
Date:
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
> > 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/
>

Re: SELECT INTO Array?

From
"Sean Davis"
Date:
----- 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.
>