<p><font size="2">Hi,<br /><br /> I'm trying to import some relatively huge (~300GiB) set of data from<br /> Microsoft
SQLServer database to PostgreSQL. For this purpose, I use<br /> bcp.exe (bulk copy utility) comes with MSSQL. Despite
thereare command<br /> line arguments which you can specify batch size, escape characters, null<br /> fields, etc.; %90
ofthese arguments are unsuprisingly ignored by<br /> bcp.exe. The problem is, bcp.exe<br /><br /> - Uses nothing to
specifynull fields, which is equivalent to<br /><br /> COPY ... WITH NULL AS ''<br /><br /> in PostgreSQL.<br
/><br/> - Uses \x00 character to specify empty strings.<br /><br /> (Sorry not any single part of this mess is
configurable.)To<br /> successfully accept such an input during "COPY ... FROM ..." in<br /> PostgreSQL, I decided to
writemiddleware triggers to tables including<br /> columns of type character varying. Below is the procedure I come up
with<br/><br /> CREATE OR REPLACE FUNCTION create_bcp_fix_triggers (_table_schema text)<br /> RETURNS void AS $$<br
/> DECLARE<br /> _table record;<br /> _column record;<br /> _create_stmt text;<br />
BEGIN<br/> SET standard_conforming_strings TO off;<br /> SET escape_string_warning TO off;<br /> --
Findevery table having a column of type 'character varying'.<br /> FOR _table<br /> IN SELECT
table_name<br/> FROM information_schema.columns<br /> WHERE table_schema = _table_schema AND<br
/> data_type = 'character varying'<br /> GROUP BY table_name<br /> ORDER BY
table_name<br/> LOOP _create_stmt = 'BEGIN;\n' ||<br /> 'CREATE OR REPLACE\n' ||<br />
'FUNCTION ' || _table.table_name || '_bcp_fix ()\n' ||<br /> 'RETURNS
"trigger"AS $bcp-fix$\n' ||<br /> 'BEGIN\n';<br /> -- Create appropriate bcp.exe fix
clausesfor every found column.<br /> FOR _column<br /> IN SELECT column_name<br />
FROMinformation_schema.columns<br /> WHERE table_schema = _table_schema AND<br />
table_name = _table.table_name<br /> LOOP _create_stmt = _create_stmt ||<br />
' IF NEW.' || _column.column_name || ' = E''\0'' THEN\n' ||<br /> ' NEW.' ||
_column.column_name|| ' = ''''\n' ||<br /> ' END IF;\n';<br /> END LOOP;<br
/> _create_stmt = _create_stmt ||<br /> ' RETURN NEW;\n' ||<br />
'END;' ||<br /> '$bcp-fix$ LANGUAGE plpgsql;\n' ||<br />
'CREATE TRIGGER ' || _table.table_name || '_bcp_fix\n' ||<br /> '
BEFOREINSERT ON ' || _table.table_name || '\n' ||<br /> ' FOR EACH ROW EXECUTE ' ||<br />
' PROCEDURE ' || _table.table_name || '_bcp_fix();\n' ||<br />
'COMMIT;';<br/> EXECUTE _create_stmt;<br /> END LOOP;<br /> END;<br /> $$ LANGUAGE plpgsql;<br
/><br/> But executing this procedure fails for some reason I couldn't<br /> understand.<br /><br /> > SELECT
public.create_bcp_fix_triggers('commsrv');<br/> ERROR: syntax error at or near "AS $"<br /> LINE 4: RETURNS
"trigger"AS $bcp-fix$<br /> ^<br /> QUERY: BEGIN;<br /> CREATE OR REPLACE<br />
FUNCTIONxyz_bcp_fix ()<br /> RETURNS "trigger" AS $bcp-fix$<br /> BEGIN<br /> IF NEW.foo = E' NEW.foo
=''<br /> END IF;<br /> IF NEW.bar = E' NEW.bar = ''<br /> END IF;<br /> RETURN NEW;<br
/> END;$bcp-fix$ LANGUAGE plpgsql;<br /> CREATE TRIGGER xyz_bcp_fix<br /> BEFORE INSERT ON xyz<br /> FOR
EACHROW EXECUTE PROCEDURE xyz_bcp_fix();<br /> COMMIT;<br /> CONTEXT: PL/pgSQL function
"create_bcp_fix_triggers"line 41 at<br /> EXECUTE statement<br /><br /> Can anybody help me to spot the problem?<br
/><br/><br /> Regards.<br /><br /> P.S. For data transfer during migratition, I first considered using "EMS<br />
DataExport for SQL Server" tool, but it lacks of performance.<br /> (~1000rows/sec) Namely, I'm open to any
bcp.exealternatives.</font>
