Stored Procedure Speed - Mailing list pgsql-general

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

LOOP

return next r.text;

END LOOP;

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