Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

あいまい検索作ってみた

あいまい検索作ってみた

テーブル名と検索ワードを指定して、全レコードの全文字列カラムから検索し
ヒットしたカラム名全部と、ヒットしたレコードの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');
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away