CODEWITHSUNDEEP
sqlpostgresqlpsql
Andy
Andy

Reputation: 3

Error: type of parameter does not match that when preparing the plan postgresql-9.3.3

I am doing an assignment by using postgresql-9.3.3 on unix, my code is as follows:

create type MatchingRecord as ("table" text, "column" text, nexamples integer);

create or replace function Q2("table" text, pattern text) returns setof MatchingRecord
as $$
declare
    record_q2 MatchingRecord;
    curs1_q2 record;
    curs2_q2 record;
    n integer;
    value text;
begin
    for curs1_q2 in select column_name from information_schema.columns where table_name = $1 
    loop
        n := 0;
        raise notice 'curs1 = %', quote_ident(curs1_q2.column_name);
        for curs2_q2 in execute 'select '||quote_ident(curs1_q2.column_name)||' as col from '||quote_ident($1) 
        loop
            value := (curs2_q2.col::text); 
            if(value ~ $2) then n := n + 1;
            end if;
        end loop;
        if (n > 0) then record_q2.table := $1; record_q2.column := curs1_q2.column_name; record_q2.nexamples := n;
        return next record_q2;
        end if;
    end loop;
    return;
end;
$$ language plpgsql;

which has two loops, in the first loop I need to get the column name from the system table information_schema.columns and then I want to do the second loop to find out the value by using select, and the column name is the content of first loop curs2_q2.column_name and the table is input parameter $1. when I executed the above code, the error appeared as :

proj2=# select * from q2('subjects', 'COMP\d\d\d');
NOTICE:  curs1 = id
NOTICE:  curs1 = code
ERROR:  type of parameter 14 (character) does not match that when preparing the plan (integer)
CONTEXT:  PL/pgSQL function q2(text,text) line 16 at assignment

how can I solve the 'plan caching' problem?

Upvotes: 0

Views: 5116

Answers (1)

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

Check this one. It works for me.

create or replace function Q2("table" text, pattern text) returns setof MatchingRecord
as $$
declare
    record_q2 MatchingRecord;
    curs1_q2 record;
    curs2_q2 record;
    n integer;
begin
    for curs1_q2 in select column_name from information_schema.columns where table_name = $1 
    loop
        n := 0;
        raise notice 'curs1 = %', quote_ident(curs1_q2.column_name);
        for curs2_q2 in execute 'select '||quote_ident(curs1_q2.column_name)||'::text as col from '||quote_ident($1) 
        loop
            if(curs2_q2.col ~ $2)   then n := n + 1;
            end if;
        end loop;
        if (n > 0) then record_q2.table := $1; record_q2.column := curs1_q2.column_name; record_q2.nexamples := n;
        return next record_q2;
        end if;
    end loop;
    return;
end;
$$ language plpgsql;

Upvotes: 2

Related Questions