Returning setof records

From: "Scott Schulthess" <scott(at)topozone(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Returning setof records
Date: 2006-01-20 16:12:18
Message-ID: 4BF377919225F449BB097CB76FFE9BC83DD7EC@ptolemy.topozone.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2006-01-20 16:39:12 Re: Returning setof records
Previous Message Tom Lane 2006-01-20 15:48:56 Re: problem with dead 'drop table' process