retrun multiple functions from dynamic query

Question

I need to return multiple fields from dynamic query.I am referring this link below code what I am doing so far

CREATE OR REPLACE FUNCTION get_names(ind_zone text,dst text,nmind text,ind text,implst text,rsvr text,n34b text,prjapr text)
RETURNS   SETOF RECORD AS 
$BODY$
DECLARE 
sql text;
BEGIN
sql:='select indzone,dstr,nmindar,plno,pltar,nmalt from plotboundary where indzone='|| quote_literal(ind_zone)||' AND dstr='||quote_literal(dst)||' AND nmindar='||quote_literal(nmind)||' AND indx='|| quote_literal(ind);
IF implst IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' implst = ' || quote_literal(implst);
END IF;
IF rsvr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' rsvr = ' || quote_literal(rsvr);
END IF;
IF n34b IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' n34b = ' || quote_literal(n34b);
END IF;
IF prjapr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' prjapr = ' || quote_literal(prjapr);
END IF;
RETURN QUERY EXECUTE sql;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_names( text,
 text, text, text, text, text, text, text)
  OWNER TO postgres;

calling function like

 select * from get_names('Tumakuru Zone','Tumakuru','Vasanthanarasapura 2nd Phase','Allotted','Yet to Start',null,null,null)
    AS t(indzone text,dstr text,nmindar text,plno text,pltar text,nmalt text);

I am getting error like

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying(20) does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function get_names(text,text,text,text,text,text,text,text) line 22 at RETURN QUERY

Answer 1

It looks like indzone is a varchar(20), not a text - you could either specify the returned record using the former, or cast indzone inside the function. (This applies to the solution suggested below, too.)

But:

As it looks like, you don't need any dynamic SQL in your function, as you never use any object (table, column, etc.) name specified on the fly. Your query could look like

SELECT indzone, dstr, nmindar, plno, pltar, nmalt 
  FROM plotboundary 
 WHERE indzone = ind_zone AND [...]
   AND implst = COALESCE(_implst, implst) -- changed the `IN` parameter name to avoid confusion
   AND rsvr = COALESCE(_rsvr, rsvr)
   AND [...]

In certain (I'd say rare) cases, this can be less efficient than you version, but that you can find out easily on your data. Most probably a well chosen index on the 'fix' columns (that are always compared to the incoming parameters) saves you worrying about it.