How can I pass a string as where clause to a function?

Question

How can I convert this:

CREATE OR REPLACE FUNCTION getFooData() 
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where x = 3;
END
$BODY$
LANGUAGE plpgsql VOLATILE

into this:

CREATE OR REPLACE FUNCTION getFooData( IN criteria text ) 
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where $1; -- > I CAN'T DO THIS
END
$BODY$
LANGUAGE plpgsql VOLATILE

I need to create a flexible query criteria in my function or else I'll be in a nightmare to create several functions to cover all foo fields combinations and pass only the values thru parameter. The problem is in some situations I may use foo.x, foo.y and foo.z in other I may use foo.h, foo.x. I may use any fields in search criteria in any combinations.

Answer 1

EDIT: Following the Horse's answer/comment, this is my final version of my function:

CREATE OR REPLACE FUNCTION public.pointscanner(IN routegeometry text, IN criteria text)
  RETURNS TABLE(way geometry, name text, distance double precision, tags hstore, operator text, admin_level text, z_order integer) AS
$BODY$
DECLARE 
    geomRoute geometry;
    routeBB box2d;
    sql text;
BEGIN
    geomRoute = ST_GeomFromText( $1 ,4326);

    routeBB := ST_Extent(geomRoute);


    sql := 'SELECT pt.way, pt.name, ST_Distance( $1, ST_Transform(pt.way,4326) ) * 111195 as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order '
     || ' FROM planet_osm_point pt where $2 && ST_Transform(pt.way,4326) and ' || criteria;


    RETURN QUERY EXECUTE sql USING geomRoute, routeBB;


END; $BODY$
  LANGUAGE plpgsql VOLATILE

Called as: select * from pointscanner(<first_arg>, 'x = 3') where second selection

Performance test: must I put the selecion criteria inside or leave it outside a function?

In this test I create a functio that select some records in a OpenStreetMap table. This is the heart of the function:

Function pointscanner():

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195 
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order 
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326) 

The fact is: I must do a second selection (fine tunning) in the function result. Must I do this inside the function and pass the selecion parameters or I can do it outside the function?

Selection exemple: name is not null

Option 1: a where clause inside the function:

Change function to:

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195 
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order 
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326) and pt.name is not null;

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=333 width=172) 
    (actual time=1251.040..1251.792 rows=358 loops=1)"
"  Filter: (round((distance)::numeric, 5) < '500'::numeric)"
"Planning time: 0.074 ms"
"Execution time: 1251.867 ms"

Option 1: a where clause outside the function:

Leave the function untouched.

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500 and name is not null

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=332 width=172)
    (actual time=4654.345..4655.075 rows=358 loops=1)"
"  Filter: ((name IS NOT NULL) AND (round((distance)::numeric, 5) < '500'::numeric))"
"  Rows Removed by Filter: 238"
"Planning time: 0.114 ms"
"Execution time: 4655.161 ms"

So I conclude I MUST do the second selection criteria INSIDE the function.