Re: COPY into a view; help w. design & patch

I'm not sure exactly why you want to do with this, but it seems very
similar to what you can already do using prepared statements. Instead
of saying

COPY INTO (statement) (column [, ...])
a1,a2,a3,...,an
b1,b2,b3,...,bn
c1,c2,c3,...,cn
\.

You could instead say:

BEGIN WORK
PREPARE somestatementhandle (column[, ...]) AS statement
EXECUTE somestatementhandle ('a1','a2','a3','...','an');
EXECUTE somestatementhandle ('b1','b2','b3','...','bn');
EXECUTE somestatementhandle ('c1','c2','c3','...','cn');
DEALLOCATE somestatementhandle
COMMIT WORK

See http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html

...Robert

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Karl O. Pinc
Sent: Friday, May 18, 2007 6:41 PM
To: pgsql-hackers(at)postgresql(dot)org
Subject: [HACKERS] COPY into a view; help w. design & patch

Hi,

I'm attempting a patch that would allow the COPY
command to copy into a view. I needed to code
something so as to get a clue, but the design has
not been discussed so I'm posting here rather
than the patches list.

I had a wee bit of discussion about this on IRC.
There was a suggestion to add generality by
to copying into a INSERT statement. However
the INSERT statement and the COPY statement
both list column names, and other issues came up
and I stopped thinking about it.

Better I think would be to have a syntax like:

COPY INTO ( statement [; , ...]) ( column [, ...] )
FROM { 'filename' | STDIN }
and so forth

Statements would then have $1, $2 type arguments
in them that correspond to the supplied column names.
The column names wouldn't mean much, really they'd
just be an indication of how many columns there are
in the input data. Statements would usually be
INSERT statements, but could be any other
sort as well. (DELETE comes to mind as useful,
as do INSERT statements that use a
SELECT ... WHERE NOT EXISTS ... sort of query
to deal with inserting into 1-to-many relationships.)

I don't know if it would be tricky
to use a semicolon as the token delimiting statements
but I presume not.

If this more general syntax were chosen the copying
into a view would just be syntactic sugar for copying
into an INSERT statement that inserted into the view.

I haven't thought a whole lot about a generalized syntax.
(The return code would be more like that of EXECUTE.)
I've been focusing on copying into a view, which is
what I want. At this point I'd much prefer just implementing
the part that copies into a view as that's already
something I need help with. (!)

As far as the patch itself, it's time to ask for help.

The basic idea is to prepare an INSERT statement into
the view and then run it in a portal for each line
of input. (This should generalize to the "more general"
copy syntax above, I hope.) I've checked
(I think) that my data structures
are the same as what I'd get if I was doing a
PREPARE and then a EXECUTE. There's issues of locking
and snapshots and probably other stuff and I thought
I was following the example of what the prepare/execute
code does but I've obviously got something wrong.

I'm having problems
debugging what I've got. It works up to the point
of the PortalRun() call, then it crashes.
The regression tests are incomplete, done only
to the point where, in copy2.sql, it crashes.

The changes to outfuncs.c can be ignored. It won't
be in the final patch. I've just been using it to
dump various data structures as can be seen from
the various bits of debugging stuff left in my patch.

I've been debugging by running "make check" and
then looking at the copy2.out section of regression.diffs.
This isn't going to work if I'm going to poke
about inside the PortalRun() call. Is there
a make target that will setup the regression
environment so that I can then run the
backend via gdb, or something? I'm working
on a system that has a live pg install on it
and need to be careful not to break that.

Please help with suggestions for design,
code, and how to debug. As you may have noticed,
this is the first time I've messed with the
pg code. I could really use a lot of help.
I've been at this for quite a while and
you can see that I've not even gotten something
to work.

Thanks.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein