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