search text global.

global_search.sql

/*
input:  search word, schemas[], verbose_is_true_or_false
output: ctids, tablename, column name.
global_search is a plpgsql function that search  _srctext in all the visible tables(table,materialized view) of the public schema (default)
if you specified schemas, then search agaginst all the visible tables in an array schemas (param schema_names).

--for each table being searched, find only 10 rows that have the searched word.
-- skipped the partititoned table.
*/
CREATE OR REPLACE FUNCTION global_search (
    _srctxt text DEFAULT NULL
    ,schema_names regnamespace[] DEFAULT '{public}'
    ,is_verbose BOOLEAN DEFAULT FALSE
)
    RETURNS TABLE (
        ctids tid
        ,tablename text
        ,column_name text
    )
    AS $$
    DECLARE rec record; _sql text;
BEGIN
    FOR rec IN WITH CTE AS (
        SELECT
            c.relname,
            pn.nspname,
            a.attname AS colname,
            pg_catalog.format_type(a.atttypid, a.atttypmod) AS datatype,
            array_agg(ARRAY[c.relowner, s.grantor, s.grantee]) AS can_be_select
        FROM
            pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace
            JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
            JOIN pg_catalog.pg_type pt ON pt.oid = a.atttypid
            CROSS JOIN aclexplode(COALESCE(c.relacl, acldefault('r'::"char", c.relowner))) s (grantor,
                grantee,
                privilege_type,
                is_grantable)
        WHERE
            -- only search in regular table, and materialize view
            c.relkind IN ('r', 'm')                                             
            AND pt.typarray > 0                                                 -- not array data type.
            AND pn.nspname NOT IN ('pg_catalog', 'information_schema')          --not in catalog schemas.
            AND a.attname NOT IN ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'ctid')
            AND (pg_catalog.format_type(a.atttypid, a.atttypmod) ~* 'char'      -- column type ~ text
                OR pg_catalog.format_type(a.atttypid, a.atttypmod) ~* 'text')   -- column type ~ text
            AND pg_catalog.format_type(a.atttypid, a.atttypmod) <> 'character(1)' 
            AND s.privilege_type = 'SELECT'                                     --must have table select priv.
        GROUP BY
            1,
            2,
            3,
            4
)
    SELECT
        nspname,
        relname,
        colname,
        datatype
    FROM
        cte
    WHERE
        can_be_select @> ARRAY['0'::oid]
        OR can_be_select @> ARRAY (
            SELECT
                oid
            FROM
                pg_catalog.pg_roles
            WHERE
                rolname = CURRENT_USER)                 --either current role can be select, or table select priv is public.
        AND nspname::regnamespace = ANY (schema_names)  --search in func input specified schemas. default as public. 
            LOOP
                --for each returned row, execute the _sql command.
                _sql := format($sql$select  ctid,%L, %L from %I.%I where %I = %L limit 10 $sql$
                                        , rec.relname, rec.colname, rec.nspname, rec.relname, rec.colname, _srctxt);
                if is_verbose then RAISE NOTICE '_sql:%', _sql; end if;
                RETURN query EXECUTE _sql;
            END LOOP;
END;
$$
LANGUAGE plpgsql;



/*
can Only search against thats that you have full select priviledges.
if you only have column specific select priviledge
    ,table is not visible to you, therefore cannot search that table.
following example, bob can search on test_s, but alice cannot.    
*/
CREATE TABLE test_s (
    a text,
    b text,
    c text
);
INSERT INTO test_s
    VALUES ('unique_a', 'unique_b', 'unique_c');
GRANT SELECT (a) ON TABLE test_s TO alice;
GRANT SELECT ON TABLE test_s TO bob;