あいまい検索作ってみた

テーブル名と検索ワードを指定して、全レコードの全文字列カラムから検索し
ヒットしたカラム名全部と、ヒットしたレコードのPK全部を出力してくれます

ところでQiitaのSQLのシンタックスハイライトって色つかないんですか...?

あいまい検索.sql

-- if exist
DROP FUNCTION FUZZY_SEARCH(VARCHAR, VARCHAR);

-- implementation
CREATE OR REPLACE FUNCTION FUZZY_SEARCH(TAB_NAME VARCHAR, KEY_WORD VARCHAR) RETURNS VARCHAR AS $$
	-- count hit cases
	DECLARE
		-- all char columns
		COLUMNS_CURSOR CURSOR IS
			SELECT
				column_name
			FROM
				information_schema.columns
			WHERE
				table_name = TAB_NAME
				AND data_type LIKE '%char%';
		-- target columns
		COLUMNS_VAR VARCHAR;
		-- counter
		CNT INTEGER;
		-- result1
		ANSWER_COLUMN VARCHAR;
		-- result2
		ANSWER_PK VARCHAR;
		-- pk select
		PK_CURSOR REFCURSOR;
		-- tmp
		ANSWER_PK_TMP VARCHAR;
		-- pk column
		PK_NAME VARCHAR;
		-- get pk sql string
		GET_PK VARCHAR :=
			'SELECT
				ccu.column_name
			FROM
				information_schema.table_constraints tb_con
				INNER JOIN information_schema.constraint_column_usage ccu
					ON tb_con.constraint_catalog = ccu.constraint_catalog
					AND tb_con.constraint_schema = ccu.constraint_schema
					AND tb_con.constraint_name = ccu.constraint_name
				INNER JOIN information_schema.key_column_usage kcu
					ON tb_con.constraint_catalog = kcu.constraint_catalog
					AND tb_con.constraint_schema = kcu.constraint_schema
					AND tb_con.constraint_name = kcu.constraint_name
					AND ccu.column_name = kcu.column_name
			WHERE
				tb_con.table_name = ''' || TAB_NAME || '''' || '
				AND tb_con.constraint_type = ''PRIMARY KEY''';
	BEGIN
		EXECUTE GET_PK INTO PK_NAME;
		ANSWER_COLUMN := '';
		ANSWER_PK := '';
		OPEN COLUMNS_CURSOR;
		LOOP
			FETCH COLUMNS_CURSOR INTO COLUMNS_VAR;
			EXIT WHEN NOT FOUND;
			-- search
			EXECUTE 'SELECT COUNT(*) FROM ' || TAB_NAME || ' WHERE ' || COLUMNS_VAR || ' LIKE ''%' || KEY_WORD || '%'';' INTO CNT;
			-- hit
			IF CNT > 0
				-- stock column name
				THEN ANSWER_COLUMN := ANSWER_COLUMN || TAB_NAME || '.' || COLUMNS_VAR || ' ,';
				-- select pk
				OPEN PK_CURSOR FOR EXECUTE 'SELECT ' || PK_NAME || ' FROM ' || TAB_NAME || ' WHERE ' || COLUMNS_VAR || ' LIKE ''%' || KEY_WORD || '%'';';
				LOOP
					FETCH PK_CURSOR INTO ANSWER_PK_TMP;
					EXIT WHEN NOT FOUND;
					ANSWER_PK := ANSWER_PK || ANSWER_PK_TMP || ',';
				END LOOP;
				CLOSE PK_CURSOR;
			END IF;
		END LOOP;
		CLOSE COLUMNS_CURSOR;
		RETURN 'Hit columns CSV ,' || ANSWER_COLUMN || 'Hit PK List CSV ,' || ANSWER_PK;
	END;
$$ LANGUAGE 'plpgsql';

-- How2call
SELECT FUZZY_SEARCH(TAB_NAME => 'table_name', KEY_WORD => 'A');