1 Temat przez garry0001

  • garry0001
  • Nowy
  • Nieaktywny
  • Zarejestrowany: 2012-10-11
  • Posty: 1

Temat: function for search string in all table of a particular schema ? (post

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

2 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: function for search string in all table of a particular schema ? (post

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

Ostatnio edytowany przez c_michal (2012-10-13 01:53:49)