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!