Hello all!
I have a very simple query that I am trying to wrap into a function:
SELECT gs.geo_shape_id AS gid,
gs.geocode
FROM geo_shapes gs
WHERE gs.geocode = 'xyz'
AND geo_type = 1
GROUP BY gs.geography, gs.geo_shape_id, gs.geocode;
This query runs in about 10 milliseconds.
Now my goal is to wrap the query in a function:
I create a return type:
CREATE TYPE geocode_carrier_route_by_geocode_result AS
(gid integer,
geocode character varying(9));
ALTER TYPE geocode_carrier_route_by_geocode_result
OWNER TO root;
..and the function
CREATE OR REPLACE FUNCTION geocode_carrier_route_by_geocode(geo_code character(9))
RETURNS SETOF geocode_carrier_route_by_geocode_result AS
$BODY$
BEGIN
RETURN QUERY EXECUTE
'SELECT gs.geo_shape_id AS gid,
gs.geocode
FROM geo_shapes gs
WHERE gs.geocode = $1
AND geo_type = 1
GROUP BY gs.geography, gs.geo_shape_id, gs.geocode'
USING geo_code;
END;
$BODY$
LANGUAGE plpgsql STABLE;
ALTER FUNCTION geocode_carrier_route_by_geocode(character)
OWNER TO root;
Execute the function: select * from geocode_carrier_route_by_geocode('xyz');
This query takes 500 milliseconds to run. My question of course is why?
Related: If I create a function and assign LANGUAGE 'sql', my function runs in the expected 10 milliseconds. Is there some overhead to using the plpgsql language?
Thanks for any help in clarifying my understanding!