Search all columns as a single text per row

Question

Like google-search or any other search-box, search "all columns as single text" is a simple and universal tool, but I not seet how to do it using tsquery/tsvector tools.

No problem if it is an extension: I need an extension that offers a full-text-search with a kind of contains(*,text) (the same * of SELECT *) instead to concatenate each column-value.

There are times when a formal structured query language is overkill, and the easiest way to find the right content is to perform a search, like you would with a search engine such as Google or Yahoo (...) JCR-SQL2:
SELECT * FROM [nt:base] WHERE CONTAINS([nt:base],'full-text-query')

Full+text+search example in other SQL engine

Answer 1

You can cast the complete row to a string (text) and then use the built-in full text search on that result:

select *
from foo
where to_tsvector(foo::text) @@ to_tsquery('full-text-query');

foo::text concatenates all columns of that table as a single large string, using the default text representation for non-text data.

Answer 2

There is no extension. That's not how SQL works. There is no "grep on a table". You can however, use the information_schema to generate a dynamic query for this. First we create a sample table,

CREATE TABLE foo (a text, b text, c text, d text);

Now we have a query,

WITH dest(catname, schemaname, tablename) AS (
  SELECT CURRENT_CATALOG::text, 'public'::text, 'foo'::text
)
SELECT
  FORMAT (
    $$ SELECT * FROM %I.%I.%I WHERE %s$$,
    dest.catname,
    dest.schemaname,
    dest.tablename,
    string_agg(
      FORMAT(
        $$to_tsvector(%I.%I.%I.%I) @@ to_tsquery('rat & fat')$$,
        table_catalog,
        table_schema,
        table_name,
        column_name
      ),
      ' OR '
    )
  )
FROM information_schema.columns
CROSS JOIN dest
WHERE table_catalog = dest.catname
  AND table_schema = dest.schemaname
  AND table_name = dest.tablename
GROUP BY dest.catname, dest.schemaname, dest.tablename;

Now you can run that in psql with \gexec, or EXECUTE is as dynamic sql.

INSERT INTO foo (a,c) VALUES ('HALLO', 'FAT RAT');
(paste query above)
\gexec

You can also write dynamic sql to concatenate each field, if that's what you want and you're just being lazy by not wanting to write it out.