full db substring search case (in)sensitive

Question

i am would like to perform for my database a full database substring search. So if a the field has ' hello world, how are you and I am looking for how. The result should show 'hello world [..bla bla bla..]'. Additionally how do I add a boolean var, which is for case sensitive oder insensitive.

I got following code, but stil need the upper stuff, to integrate. Any recommondation?

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

cheers

Answer 1

This is how i did it now:

package src;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.util.regex.Pattern;



/*
PATTERN MATCHING
*/
public class BigSearch {

    public static void main(String[] args) {

            try {

                String keyword;
                String schema = "public";
                Boolean caseAware = true;

                System.out.println("Insert the term we shall look for in the database.");
                Scanner s = new Scanner(System.in);
                keyword = s.nextLine();

                System.out.println("Do you want the search to be case sensitve "
                        + "\n1 - case sensitive"
                        + "\n0 - case insensitive");

                int caseAwareInt = s.nextInt();

                while (caseAwareInt != 0 && caseAwareInt != 1) {
                    System.out.println("You need to enter 1 or 0. Enter again!");
                    caseAwareInt = s.nextInt();
                }


                if (caseAwareInt == 1) {
                    caseAware = true;
                } else if (caseAwareInt == 0) {
                    caseAware = false;
                }

                System.out.println("Your search is now case ");
                if (caseAware) {
                    System.out.println("sensitive!");
                }
                if (!caseAware) {
                    System.out.println("insensitive!");
                }

                String like = "";

                if (caseAware) {
                    like = "LIKE";
                } else {
                    like = "ILIKE";
                }

                Connection conn;
                Connectivity connectivity = new Connectivity();
                conn = connectivity.getConnection();
                Statement stmt = conn.createStatement();
                Statement stmt2 = conn.createStatement();
                Statement stmt3 = conn.createStatement();
                Statement stmt4 = conn.createStatement();
                Statement stmt5 = conn.createStatement();

                ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE schemaname = '" + schema + "';");
                ResultSet tablenames = stmt2.executeQuery("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = '" + schema + "';");
                rs.next();
                int counttables = rs.getInt(1);
                System.out.println("Tabellen im Schema: " + counttables);

                int appearance = 0;
                int diftables = 0;

                for (int i = 0; i < counttables; i++) {
                    tablenames.next();
                    ResultSet columnnames = stmt3.executeQuery("SELECT column_name " +
                            "FROM information_schema.columns " +
                            "WHERE table_schema = '" + schema +
                            "'  AND table_name = '" + tablenames.getString(1) + 
                            "'  AND data_type = 'character varying'");

                    ResultSet rss = stmt4.executeQuery("SELECT COUNT(*) " +
                            "FROM information_schema.columns " +
                            "WHERE table_schema = '" + schema +
                            "'  AND table_name   = '" + tablenames.getString(1) +
                            "'  AND data_type = 'character varying'");

                    rss.next();
                    int countcolumns = rss.getInt (1);
                    System.out.println("Spalten in der Tabelle " + tablenames.getString(1) + ": " + countcolumns);

                    int count = 0;

                    for (int i2 = 0; i2 < countcolumns; i2++) {
                        columnnames.next();
                        columnnames.getString(1);

                        System.out.println("Spaltenname: " + columnnames.getString(1));
                        System.out.println("Tabelle: " + tablenames.getString(1));

                        ResultSet containsString;


                        containsString = stmt5.executeQuery("SELECT * "
                                + "FROM " + tablenames.getString(1)
                                + " WHERE " + columnnames.getString(1) + " " + like + " '%" + keyword + "%'");

                        while (containsString.next()) {
                            System.out.println(containsString.getString(1) + " -- contains your keyword");
                            appearance++;
                            count ++;
                        }
                    }
                    if (count > 0) {
                        diftables ++;
                    }
                }

                System.out.println("The keyword was found " + appearance + " times in " + diftables + " different tales.");

            } catch (SQLException e) {
                e.printStackTrace();
            } 

    }

}

Answer 2

Admittedly, I did not compile this last night -- if I did, I would have seen the error. You are right, the optional parameters have to go at the end. Now that I did have a chance to compile, this did work for me.

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    ignore_case boolean,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, column_contents text)
AS $$
declare
  like_function text := 'like';
  nneedle text;
begin

  nneedle := '%' || needle || '%';

  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE'
  LOOP
    if ignore_case then
      like_function := 'ilike';
    end if;

    execute format('SELECT %I FROM %I.%I WHERE cast(%I as text) %s %L',
       columnname, schemaname, tablename, columnname, like_function, nneedle
    ) into column_contents;

    IF column_contents is not null THEN
      RETURN NEXT;
    END IF;

 END LOOP;
END;
$$ language plpgsql;

Here is an example of the usage:

select *
from search_columns('hambone', true, array['address_master'], array['sales']);

And the results:

schemaname     tablename      columnname     column_contents
-----------    ----------     ------------   ---------------------------
sales          address_master address_desc   Christopher "Hambone" Hamel