CREATE OR REPLACE FUNCTION f_assume_you_must_use_files()
RETURNS TABLE (table_schema VARCHAR(128), table_name VARCHAR(128), column_name
VARCHAR(128)) AS $$
DECLARE
sql_stmt TEXT;
cnt BIGINT;
varchar_columns RECORD;
BEGIN
RAISE NOTICE 'Detecting possible occurrences of the antipattern "Assume You
Must Use Files"';
FOR varchar_columns IN SELECT c.table_schema, c.table_name, c.column_name
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t
USING (table_schema, table_name) WHERE c.data_type IN ('character varying', 'text')
AND t.table_type='BASE TABLE' AND 
c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY c.table_schema, c.table_name LOOP
table_schema:= varchar_columns.table_schema;
table_name:= varchar_columns.table_name;
column_name:= varchar_columns.column_name;
sql_stmt:='SELECT Count(' || quote_ident(column_name) || ') AS c FROM
' || quote_ident(table_schema) ||'.' || quote_ident(table_name) || ' WHERE '||
quote_ident(column_name) || '~''^.*/.*\.[A-Za-z]{3}$''';
/*The source of the regular expression:
http://stackoverflow.com/questions/6416065/c-sharp-regex-for-file-paths-e-g-c-testtest-exe*/
RAISE NOTICE '%', sql_stmt;
EXECUTE sql_stmt INTO cnt;
IF cnt>0 THEN
RETURN NEXT;
END IF;
END LOOP;
RAISE NOTICE 'Detection completed';
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = information_schema, pg_temp;
SELECT * FROM f_assume_you_must_use_files();

DROP FUNCTION IF EXISTS f_assume_you_must_use_files();