CODEWITHSUNDEEP
sqlpostgresql
Ya Guy Godzilla
Ya Guy Godzilla

Reputation: 107

Postgresql:return results as found not at end of search

I have a massive, horribly constructed database and I am running a query through it to find a particular piece of data and let me know its table and column. Is there a way to make the query return data each time it finds something instead of at the end?

below is the query for those interested

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;

--Search in all tables within public schema:
select * from search_columns('E0801');

Search in a specific table:

select * from search_columns('foobar','{w}');

Search in a subset of tables obtained from a select:

select * from 
grep_columns('foobar', array(select table_name::name from information_schema.tables where table_name like 's%'), array['public']);

Get a result row with the corresponding base table and and ctid:

select * from public.w where ctid='(0,2)';

Upvotes: 1

Views: 142

Answers (2)

Richard Huxton
Richard Huxton

Reputation: 22952

You can declare a CURSOR and FETCH using that. It will need to be inside an explicit transaction.

BEGIN;
DECLARE CURSOR mycurs FOR SELECT * FROM search_columns(...);
FETCH FORWARD 5 FROM mycurs;
-- repeat as needed
ROLLBACK;

If you are happy to modify the function you can have it return a REFCURSOR. Doing this can allow PostgreSQL to alter your plan to return some rows early (although this can make the overall cost of the query higher). For your particular example this seems unlikely to happen.

Upvotes: 0

klin
klin

Reputation: 121794

You can use RAISE NOTICE provided that your client program shows notices asynchronously.

Insert RAISE NOTICE before RETURN NEXT and try it in psql:

...
IF rowctid is not null THEN
  RAISE NOTICE '% % % %', schemaname, tablename, columnname, rowctid;
  RETURN NEXT;
END IF;
...

Upvotes: 1

Related Questions