Re: Array of C integers to temporary table?

> Hello.

> What's the "correct" (read: simple, efficient) way to
> pass an arbitrarily sized array of C integers to postgres
> and have it turned into a temporary table?

> I'm using PostgreSQL 7.4.

> I could, of course, turn the array into a long list of
> insert statements:

>  BEGIN;
>  CREATE TEMPORARY TABLE temp_table (id integer) ON COMMIT DROP;
>  INSERT INTO temp_table VALUES (1);
>  INSERT INTO temp_table VALUES (23);
>  INSERT INTO temp_table VALUES (3889);
  ...

> But that seems long winded and rather inefficient.

> Any help appreciated!

I'm not quite sure what you mean by an array of C integers.  You mean
you have an array in C
that you are trying to bring into PostgreSQL, or a random assortment of
integers in C you would like to bring in,
or you have a PostgreSQL array.

For a PostgreSQL array, I recall this working even in PostgreSQL 7.4

SELECT ary[i] AS c_val
FROM generate_series(1, array_upper(ary,0)) As i

Where ary is the name of your array.

You can then use the above as a subselect in another query

SELECT foo.*
FROM (SELECT ary[i] AS c_val
FROM generate_series(1, array_upper(ary,0)) As i) As foo

 or dump into a temp table

INSERT INTO temp_table(id)
SELECT ary[i] AS c_val
FROM generate_series(1, array_upper(ary,1)) As i


So a hard-coded example

SELECT (ARRAY[1,2,3])[i] AS c_val
FROM generate_series(1, array_upper(ARRAY[1,2,3],1)) As i

Hope that helps,
Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.