It's complicated, and a bit fragile, but possible. Mine looks something like this:
Code: Select all
uses Classes, Math, PgAccess, PgClasses, PgError, PgSQLNet, PgSQLProtocol, DBAccess, CRTypes;
procedure StreamToTable(Connection: TPgConnection; TableName: String; Stream: TStream);
const
C_BufferSize = 256*1024; // Experimentally determined optimum; yours may be different
C_ClientErrorMessage = 'COPY terminated by client';
var
Net: TPgSQLNet;
Buffer: TBytes;
ByteCount: Integer;
Query: TPgQuery;
Protocol: TPgSQLProtocol;
begin
Connection.Connect; // Make sure IConnection is assigned
Protocol := (TDBAccessUtils.GetIConnection(Connection) as TPgSQLConnection).GetProtocol;
Query := TPgQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'COPY ' + TableName + ' FROM STDIN';
Query.Prepare; // Necessary to suppress automatic UnPrepare while we're in COPY mode
Query.ExecSQL;
try
Net := nil;
while Stream.Position < Stream.Size do begin
Protocol.BeginCopyDataBlock(Net);
try
ByteCount := Min(Stream.Size - Stream.Position, C_BufferSize);
SetLength(Buffer, ByteCount);
Stream.ReadBuffer(Buffer, ByteCount);
Net.WriteBytes(TValueArr(Buffer), 0, ByteCount);
finally
Protocol.EndCopyDataBlock;
end;
end;
Protocol.PutCopyEnd;
except
on E: Exception do begin
if Protocol.ProtocolState = psCopyIn then begin
// We're still in COPY mode, i.e. error was client-side. Tell the server.
try
Net.WriteAnsiChar(PG_MSG_COPY_FAIL);
Net.WriteInt32(4 + Length(C_ClientErrorMessage) + 1);
Net.WriteAnsiString(C_ClientErrorMessage);
Net.FlushSend;
Net.WriteAnsiChar(PG_COMMAND_SYNC);
Net.WriteInt32(4);
Net.FlushSend;
Protocol.ProcessMessageQueue;
except
on E: EPgError do begin
// query_canceled is the expected response to a CopyFail; swallow it
if E.ErrorCode <> '57014' then begin
raise;
end;
end;
end;
end;
raise;
end;
end;
finally
Query.Free;
end;
end;
It's up to you to ensure that the connection's client_encoding matches the TStream's contents.
If you want the affected record count, I think that requires changes to the source (as does COPY TO STDOUT support).