Hey Ya’ll, I’m a little puzzled by the speed of the stored
procedures I am writing. Here is the query alone in pgAdmin select distinct featuretype from gnis_placenames where
state='CT' TIME: 312+16ms Here is a stored procedure create or replace function getfeaturetypes(text) returns
setof text as $$ select distinct featuretype from gnis_placenames where
state=$1; $$ language sql; TIME: 2391+15ms Now if I hardcode the stored procedure with the input create or replace function getfeaturetypes(text) returns
setof text as $$ select distinct featuretype from gnis_placenames where
state=’CT’; $$ language sql; TIME: 312+16ms I also tried plPgsql CREATE OR REPLACE FUNCTION getfeaturetypes(text) returns
setof text as $$ declare r record; begin for r in SELECT featuretype as text from gnis_placenames
where state=$1 group by featuretype order by featuretype asc return next r.text; END return; end; $$ language plpgsql; grant execute on function getfeaturetypes(text) to tzuser; TIME: 2609+16ms What gives? How can I speed up this stored procedure? -Scott |