Hello,



I'm trying to return multiple records from a function. I'm having a
little trouble getting past the "ERROR: wrong record type supplied in
RETURN NEXT

CONTEXT: PL/pgSQL function "placenamesearch" line 5 at return next"
I've had this error before and fixed it by defining columns analogous to
the returned columns to output the records into. However I can't fix it
this time. Thanks for your help ahead of time!



<code>

create function placenamesearch(place text, state integer, county text,
place text, match text) returns setof record as '

declare myview record;

begin

FOR myview IN SELECT featurename, countyname, state, featuretype,
elevation, cellname, primarylatdd, primarylondd from gnis_placenames
where featurename like place and statefips=state and countyname=county
limit 200

LOOP

return next myview;

END LOOP;

return;

end; '

language 'plpgsql';

</code>



Here's my select statement



select * from placenamesearch('Cheshire',9,'New Haven','text', 'text')
as (FeatureName varchar, CountyName varchar, State varchar, featuretype
varchar, Elevation int4,CellName varchar, PrimaryLatDD float8,
PrimaryLonDD float8);



Now here's my table definition



state char(2),

featurename varchar(96),

featuretype varchar(24),

countyname varchar(64),

statefips int4,

countyfips int4,

primarylatdd float8,

primarylondd float8,

elevation int4 DEFAULT -1,

cellname varchar(32),



So I was thinking that I was just using the shortened notation of the
column data types wrong. I tried writing them out 'in full' aka
varchar(64), etc but that didn't work either. THANKS!



-Scott



Scott Schulthess

"Scott Schulthess" <scott@topozone.com> writes:
> select * from placenamesearch('Cheshire',9,'New Haven','text', 'text')
> as (FeatureName varchar, CountyName varchar, State varchar, featuretype
> varchar, Elevation int4,CellName varchar, PrimaryLatDD float8,
> PrimaryLonDD float8);
....
> state char(2),

You've got the State output column declared as varchar, but actually
it's char. PG will often do automatic conversion from one type to the
other, but in this context it's picky...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings