Thread: 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!

postgres-novice@coreland.ath.cx writes:
> 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.

Well, the first thing to do is update to a non-dinosaurian version ;-).
Then you could use multi-row INSERT ... VALUES.  (The list of other
benefits is far too long to enumerate here, but I'll just mention that
7.4 is going to stop being maintained any day now.)

An alternative that should work on 7.x is to use COPY FROM STDIN,
but it's not really "simple" from a coding standpoint.  See the libpq
COPY-support functions.

            regards, tom lane

> 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.

On 2009-01-27 14:36:02, Tom Lane wrote:
> postgres-novice@coreland.ath.cx writes:
> > 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.
>
> Well, the first thing to do is update to a non-dinosaurian version ;-).
> Then you could use multi-row INSERT ... VALUES.  (The list of other
> benefits is far too long to enumerate here, but I'll just mention that
> 7.4 is going to stop being maintained any day now.)

I agree absolutely. Unfortunately I don't actually control the database
in question, I'm just a "guest" on it...

> An alternative that should work on 7.x is to use COPY FROM STDIN,
> but it's not really "simple" from a coding standpoint.  See the libpq
> COPY-support functions.

Ok, thanks.

On 2009-01-27 14:47:22, Obe, Regina wrote:
>
> 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.

Hi.

Yes, should've been clearer about that really. What I meant was an array
of integers in C that I want to get into PostgreSQL somehow:

void
select_all_matching (int *ids)
{
  /* SELECT * FROM some_table WHERE id IN (SELECT * FROM ids); */
}

Given the ancient version of Postgres I'm being asked to use, it looks
like I'll have to use the COPY functions in libpq or just do things the
long way...

thanks for your time!