Re: Multiple inserts without COPY

On Mar 3, 2004, at 10:22 PM, Tom Lane wrote:

> Mark Lubratt <mark.lubratt@indeq.com> writes:
>> The deletes look something like
>> delete from CL where CL_id = i
>> where i could be a list of several hundred integers.  Again, right now
>> I iterate through the list.
>
> Consider
>     delete from CL where CL_id in (i,j,k,...);
> If you have hundreds of target values, it might be better to put them
> in
> a temp table and go
>     delete from CL where CL_id in (select id from temp_table);
> The latter should be reasonably quick in 7.4, but be warned that it'll
> suck in prior releases.
>

Yeah, that's what I was looking for!  I thought I might be able to do
that.  Cool.

>> I was hoping I might be able to use COPY, but I see that's
>> really only for psql.
>
> Huh?  You can use COPY FROM STDIN in most of our client libraries,
> certainly so with libpq.  What are you using?
>

Actually, I'm using REALbasic.  All the communication is happening
through a TCP connection.  I tried emulating what the command might
look like in pgAdmin.  But, of course, after the semi-colon, the parser
got confused when it hit the actual data.  I tried:

COPY MyTable (id, val) FROM STDIN;
2    Hello There!
\.

It choked at the 2.  I was just trying to see if the backend suspended
parsing and would just start copying like psql does.  But, I guess not.
  How does psql shovel a COPY at the backend?

Oooh.  I just remembered.  There is a new method in the REALbasic
classes that provide the PostgreSQL functionality.  I'll have to check
it out...

I was hoping that there might be a syntax trick  with INs or something
like the delete command above.  Something that might expand in the
parser to do what I want to do.

Thanks!

Mark