Saturday, October 4, 2014

string search in database postgres

I created a search string function in postgres, thru which we can search string in a database table having column datatype 'character', 'character varying', 'text'.

According to the requirement we can add more datatype to vast your search or you can cut down the data-type to narrow your search.

This is one requirement, which is covered.
Now using same function we can learn below topic
  • for loop in postgres
  • dynamic execution in postgres (EXECUTE)
  • return record set using function using SETOF
    •  To achieve this i learn that how to return multiple row (record set) from a function, i.e what i want to share with all of you guy
                    First of all we need to use SETOF with return keyword, second is need to declare a variable having record datatype. third is Need to use RETURN NEXT in for loop - it will return the rows to outer RETURN, which is earlier declared.

drop function seing(char(50))

CREATE OR REPLACE FUNCTION seing(str char(50))
RETURNS setof  character varying AS
    tempCount bigint ;
    record_v record;
    query text;
    query:= 'select table_name ,column_name from information_schema.columns where table_schema = ''public'' and
        data_type in (''character'',''character varying'',''text'')';
    FOR record_v IN EXECUTE query LOOP
        EXECUTE 'select count(*) as a from ' || quote_ident(record_v.table_name) || ' where ' || quote_ident(record_v.column_name ) || ' like ''%'||str ||'%'';' into tempCount;
        --RAISE NOTICE 'loop test .. it is working fine';
        return next record_v.table_name ||'    '   ||record_v.column_name  || '   '||tempcount ;
        --return   record_v.table_name ||' '||record_v.column_name;
COST 100;

select seing('tbsp')

No comments:

Post a Comment

web stats