Mirza Related Posts

I have created a simple plug-in for showing category based related posts for wordpress.

Advertisements

how to search entire database on postgresql ?

select * from search_columns(‘%OTE5MWY4NzEzIn0=%’);

-- Function: search_columns(text, name[], name[])

-- DROP FUNCTION search_columns(text, name[], name[]);

CREATE OR REPLACE FUNCTION search_columns(
IN needle text,
IN haystack_tables name[] DEFAULT '{}'::name[],
IN haystack_schema name[] DEFAULT '{public}'::name[])
RETURNS TABLE(schemaname text, tablename text, columnname text, rowctid text) AS
$BODY$
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', */
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) LIKE %L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION search_columns(text, name[], name[])
OWNER TO weakapi;