Skip to content

Instantly share code, notes, and snippets.

@a-c-m

a-c-m/autocomplete.sql

Last active January 29, 2025 18:50
Show Gist options
  • Save a-c-m/2a5078e9aca0b98135e67c0d1336ad3f to your computer and use it in GitHub Desktop.
Save a-c-m/2a5078e9aca0b98135e67c0d1336ad3f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. a-c-m revised this gist Jan 29, 2025. 1 changed file with 3 additions and 6 deletions.
    9 changes: 3 additions & 6 deletions autocomplete.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,26 +2,23 @@ CREATE OR REPLACE FUNCTION dynamic_autocomplete(
    table_name TEXT,
    field_name TEXT,
    search_input TEXT
    ) RETURNS TABLE(count INT, field TEXT)
    ) RETURNS TABLE(count INT, value TEXT)
    LANGUAGE plpgsql AS
    $$
    DECLARE
    query TEXT;
    BEGIN
    -- Construct the dynamic SQL query
    query := format(
    'SELECT COUNT(*)::INT, MIN(%I)::TEXT
    'SELECT COUNT(*)::INT, MIN(%I)::TEXT AS value
    FROM %I
    WHERE LOWER(%I) LIKE LOWER(''%%%s%%'')
    GROUP BY LOWER(%I)
    ORDER BY COUNT(*) DESC;',
    field_name, table_name, field_name, search_input, field_name
    );

    -- Log the query for debugging
    RAISE NOTICE 'Executing SQL: %', query;

    -- Execute the query
    -- Execute the query and return results
    RETURN QUERY EXECUTE query;
    END;
    $$;
  2. a-c-m created this gist Jan 29, 2025.
    27 changes: 27 additions & 0 deletions autocomplete.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    CREATE OR REPLACE FUNCTION dynamic_autocomplete(
    table_name TEXT,
    field_name TEXT,
    search_input TEXT
    ) RETURNS TABLE(count INT, field TEXT)
    LANGUAGE plpgsql AS
    $$
    DECLARE
    query TEXT;
    BEGIN
    -- Construct the dynamic SQL query
    query := format(
    'SELECT COUNT(*)::INT, MIN(%I)::TEXT
    FROM %I
    WHERE LOWER(%I) LIKE LOWER(''%%%s%%'')
    GROUP BY LOWER(%I)
    ORDER BY COUNT(*) DESC;',
    field_name, table_name, field_name, search_input, field_name
    );

    -- Log the query for debugging
    RAISE NOTICE 'Executing SQL: %', query;

    -- Execute the query
    RETURN QUERY EXECUTE query;
    END;
    $$;