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
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 |