bcp.exe Fix Triggers - Mailing list pgsql-sql

<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>