<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
	<title type="html"><![CDATA[Forum PostgreSQL - function for search string in all table of a particular schema ? (post]]></title>
	<link rel="self" href="http://forum.postgresql.org.pl/extern.php?action=feed&amp;tid=1407&amp;type=atom"/>
	<updated>2012-10-12T23:50:58Z</updated>
	<generator>PunBB</generator>
	<id>https://forum.postgresql.org.pl/viewtopic.php?id=1407</id>
		<entry>
			<title type="html"><![CDATA[Odp: function for search string in all table of a particular schema ? (post]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=3754#p3754"/>
			<content type="html"><![CDATA[Sorry bat my english is not good

this select generate script for all tables in schema public (postgreSql 9.1)

select string_agg('select count (*) ,'''||table_name||''' from '||table_name||' where ' ||where_text,' union all ') from
(
select table_schema||'.'||table_name as table_name ,string_agg(column_name || ' ilike ''%search_text%''',' or ') as where_text
from information_schema.columns where data_type in ('character','character varying','text') and table_schema ='public'
group by table_schema||'.'||table_name
) d]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2012-10-12T23:50:58Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=3754#p3754</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[function for search string in all table of a particular schema ? (post]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=3752#p3752"/>
			<content type="html"><![CDATA[Hi ,

i want to create a function postgresql, that can able to search a string from all tables.
I try as below.... please rectify this 

CREATE OR REPLACE FUNCTION search_string(str char(50)) 
returnS character varying AS
$BODY$
DECLARE
tempCount bigint ;
record_v record;
itemid_v bigint ; 
query varchar; 
return_v character varying := null; 
BEGIN 
for record_v in (select table_name ,column_name 
from information_schema.columns and data_type in ('character','character varying','text')) loop
query := 'select count(*) from '|| record_v.table_name ||' where ' || record_v.column_name || ' like ''%' || str ||'%''' ;
execute query into tempcount;
if (tempCount >0) then
return 'l';
else
return '2572';
end if;
end loop;
END;
$BODY$ LANGUAGE plpgsql VOLATILE
COST 100;


in output i need all tables in which string exists :
like table_name count_of_string_match]]></content>
			<author>
				<name><![CDATA[garry0001]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1492</uri>
			</author>
			<updated>2012-10-11T14:04:24Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=3752#p3752</id>
		</entry>
</feed>
