> On 1/4/16 12:18 PM, Luke Coldiron wrote: > > Is there a way to achieve the performance of the COPY FROM STDIN command > > within a C extension function connected to the db connection that called > > the C function? I have text that I would like to receive as input to a C > > function that contains many COPY command statements in the file that > > would be parsed similar to how psql would handle the file but I don't > > want to shell out to psql as I need to do all of this work on the db > > connection that the function was called from as there are other commands > > that I need to perform as well after before and after handling the COPY > > commands on this same connection. I would like the unit of work to be > > all or nothing and have the performance of the COPY FROM STDIN command > > and not break things out into SELECT INTO or INSERT statements for > > performance. > > > > Ideally I would like to be able to attach to the calling db connection > > via SPI_connect() and then use the libpq library to issue the copy > > commands via PQputCopyData, PQputCopyEnd. > > C functions can use SPI, so I'm not sure what the issue is? > > http://www.postgresql.org/docs/9.5/static/spi.html > > (BTW, you'll want to scroll to the bottom of that page...) > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX > Experts in Analytics, Data Architecture and PostgreSQL > Data in Trouble? Get it in Treble! http://bluetreble.com/ I should probably back the boat up. I'm not too familiar with the inter-workings of postgres. I have a general understanding of the SPI API and realize that other c functions can be called. Part of the problem is I'm not sure which ones. The first thing I am trying to figure out is if I can perform a COPY FROM STDIN command via a C extension function using the SPI. >From what I read in the documentation it seemed to indicate that this may not be possible (http://www.postgresql.org/docs/9.3/static/spi-spi-execute.html). Here is what I have tried thus far. #include "postgres.h" #include "fmgr.h" #include "funcapi.h" #include "executor/spi.h" #include "catalog/pg_type.h" #include "commands/copy.h" // Attempt #1 Datum copy_test(PG_FUNCTION_ARGS) { SPI_connect(); int spi_status = SPI_execute("COPY public.test_table(val1, val2) FROM stdin;" /* command */, false, /* read_only */ 0 /* count */); if (spi_status != SPI_OK_SELECT) { ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg_internal("Failed: %s.", SPI_result_code_string(spi_status)))); } SPI_finish(); PG_RETURN_VOID(); } When I run the function above I get this error: ERROR: Failed: SPI_ERROR_COPY. Which is what I would expect from the documentation. However if I try something like this: // Attempt #2 Datum copy_test(PG_FUNCTION_ARGS) { SPI_connect(); SPIPlanPtr plan = SPI_prepare("COPY public.test_table(val1, val2) FROM stdin;" /* command */, 0 /* nargs */, NULL /* argtypes*/); if (plan == NULL) { ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg_internal("Failed: %s.", SPI_result_code_string(SPI_result)))); } SPI_finish(); PG_RETURN_VOID(); } It works, although I really haven't got to the point of doing anything. It looked like the "commands/copy.h" has the interface that I want to use but I'm not sure if I am even going about it the correct way. Here roughly what I am thinking I need to do. // Attempt #3 Datum copy_test(PG_FUNCTION_ARGS) { SPI_connect(); SPIPlanPtr plan = SPI_prepare("COPY public.test_table(val1, val2) FROM stdin;" /* command */, 0 /* nargs */, NULL /* argtypes*/); if (plan == NULL) { ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg_internal("Failed: %s.", SPI_result_code_string(SPI_result)))); } uint64 processed; Oid table_oid = DoCopy(const CopyStmt *stmt, "COPY public.test_table(val1, val2) FROM stdin;" /* queryString */, &processed /* processed */); //CopyState cstate = BeginCopyFrom(table_oid /* rel */, NULL /* filename */, false /* is_program */, List *attnamelist, List *options); // Somehow // End the copy command // EndCopyFrom(cstate); // TODO: Make use of the callback // extern void CopyFromErrorCallback(void *arg); // extern DestReceiver *CreateCopyDestReceiver(void); SPI_finish(); PG_RETURN_VOID(); } In the example above I'm not sure if I can use some sub struct of the SPIPlanPtr and hand it off to the DoCopy function as the CopyStmt or if I need to go about this entirely different. Any advice on the matter would be much appreciated. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
