CREATE OR REPLACE FUNCTION import_single_line_format(text, text, text) RETURNS void AS $$
-- import file containing molecule sources in (CX)SMARTS or (CX)SMILES format
-- USAGE:
-- SELECT import_single_line_format('<absolute_path_to_my_file_on_server>', '<new_table_name>', '<molecule_type>');
-- example:
-- SELECT import_single_line_format('/home/myuser/molecules.smiles', 'molecules', 'sample');

DECLARE
	file_name ALIAS FOR $1;
	valid_table_name ALIAS FOR $2;
	mol_type ALIAS FOR $3;
	invalid_table_name text;
	temp_table_name text;
	r record;
	invalid_mol_counter bigint;
	
BEGIN
	temp_table_name := valid_table_name || '_temp';

	EXECUTE 'CREATE TABLE IF NOT EXISTS ' || temp_table_name || ' (src text)';
	EXECUTE 'COPY ' || temp_table_name || '(src) FROM ''' || file_name || ''' (FORMAT csv)';

	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) || '))';
	EXECUTE 'CREATE TABLE IF NOT EXISTS ' || invalid_table_name || ' (src text)';
	
	FOR r IN EXECUTE 'SELECT * FROM ' || temp_table_name LOOP
		IF (is_valid_molecule(r.src)) THEN
			EXECUTE 'INSERT INTO ' || valid_table_name || ' VALUES (' || quote_literal(r.src)  || ')';
		ELSE
			EXECUTE 'INSERT INTO ' || invalid_table_name || ' VALUES (' || quote_literal(r.src)  || ')';
			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;
	EXECUTE 'DROP TABLE ' || temp_table_name;
END;
$$ LANGUAGE plpgsql;