Re: Multiple inserts without COPY

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

> Mark Lubratt <mark(dot)lubratt(at)indeq(dot)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