postgresql - Use geometry data type in dynamic query -

i creating function in postgresql postgis accept tables name , geometry of point , want use them in query inside function......

create or replace function trial(tbl text[],tag text[],geo geometry) returns boolean $body$ declare integer; declare len integer; declare result boolean; declare bool boolean;  begin     result=true;     select array_length(tbl,1) len;     in 1..len loop     execute format('select st_dwithin(geo::geography,geom::geography,1) %s name=any(array[%s]) , st_dwithin(geo::geography,geom::geography,1)=true',tbl[i],tag[i]) bool;          if (bool!=true)         result=false;         exit;         end if;      end loop;     return result; end; $body$ language plpgsql volatile cost 100; 

this shows me error as

line 1: select st_dwithin(geo::geography,geom::geography,1) grn...  

when use

%s geometry

i next error

error:  syntax error @ or near "aa40e9a0e5440f498329474092a40" line 1: select st_dwithin(0101000020e6100000002aa40e9a0e5440f4983294... 

i beginner in postgresql... format specifier can use in case?????

you have few issues function.

first of all, in format() function can splice in identifiers , literal values while need pass geography , text[], both parameters of function. means cannot use format() function should instead use using clause. this, incidentally, news because format() produces string , quite inefficient (you pass in geometry gets converted string format() , parsed geometry in server, once each iteration of loop). geo parameter, being pl/pgsql variable, not resolved execute must supplied through using clause.

the dynamic query not particularly written either. logic says want return true if @ least 1 row having 1 of tags located within 1 meter geo. efficiently written, query be:

select 1 <table> name = any(<tags>)   , st_dwithin(<geo>, geom::geography, 1, false) limit 1; 

this assumes tables have name , geom column. if geom of geography type can omit cast. use false calculations on sphere, more adequate short distance , faster calculate.

together flow logic improvements, function becomes:

create or replace function trial(tbl text[], tag text[], geo geography) returns boolean $body$ declare   t   text;   res int; begin   foreach t in array tbl loop      execute 'select 1 ' || quote_ident(t) ||             '  name = any($1)' ||             '    , st_dwithin($2, geom::geography, 1, false) ' ||             'limit 1'         res using tags, geo;      if res null then     -- no value returned no nearby points       return false;     end if;   end loop;   return true;              -- if here, tables have @ least 1 match end; $body$ language plpgsql;

this function takes array of table names , determines if tables have @ least 1 point of supplied tags within 1 meter of specified point. not sound useful me. returning set of text each row giving name of table (or not) meet criterion looks far more useful, or perhaps returns table (tbl name, num int, tags text[]) version gives each table number of points within 1 meter , matching tags table.