CREATE OR REPLACE FUNCTION import_sdf(text, text, text) RETURNS void AS $$
-- import SDFile containing molecule sources
-- USAGE:
-- \set <variable_name> `cat <path_to_your_sdf_file>`;
-- SELECT import_sdf(:'<variable_name>', '<new_table_name>', '<molecule_type>');
-- EXAMPLE:
-- \set content `cat ~/a.sdf`;
-- SELECT import_sdf(:'content', 'molecules', 'sample');

DECLARE
	mol_src ALIAS FOR $1;
	valid_table_name ALIAS FOR $2;
	mol_type ALIAS FOR $3;
	invalid_table_name text;
	mol_record_candidate record;
	invalid_mol_counter bigint;	
BEGIN
	invalid_table_name:= valid_table_name || '_error';
	invalid_mol_counter:= 0;
	EXECUTE 'CREATE TABLE IF NOT EXISTS ' || valid_table_name || ' (mol molecule(' || quote_literal(mol_type) || '), props hstore)';
	EXECUTE 'CREATE TABLE IF NOT EXISTS ' || invalid_table_name || ' (src text)';
	
	FOR mol_record_candidate IN SELECT * FROM parse_sdf(mol_src) LOOP
		IF (mol_record_candidate.isvalid) THEN
			BEGIN
				EXECUTE 'INSERT INTO ' || valid_table_name || ' VALUES (' || quote_literal(mol_record_candidate.molsrc)  || ', ' || quote_literal(mol_record_candidate.props) || ')';
			EXCEPTION
			WHEN OTHERS THEN
				EXECUTE 'INSERT INTO ' || invalid_table_name || ' VALUES (' || quote_literal(mol_record_candidate.molsrc)  ||')';
				invalid_mol_counter:= invalid_mol_counter + 1;
			END;
		ELSE
			EXECUTE 'INSERT INTO ' || invalid_table_name || ' VALUES (' || quote_literal(mol_record_candidate.molsrc)  ||')';
			invalid_mol_counter:= invalid_mol_counter + 1;
		END IF;			
	END LOOP;
	IF (invalid_mol_counter > 0) THEN
		RAISE NOTICE USING MESSAGE = invalid_mol_counter || ' record(s) could not be recognized as molecule(s). See table ' || invalid_table_name || ' for more information.';	
	END IF;
END;
$$ LANGUAGE plpgsql;