Thread: Returning setof records
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
Tom -
I've also tried this select * from placenamesearch('Cheshire',9,'New
Haven','','') as (FeatureName varchar, CountyName varchar, State char,
Cellname varchar, Elevation int4, PrimaryLatDD float8, PrimaryLonDD
float8);
ERROR: wrong record type supplied in RETURN NEXT
:( I realize it often automatically converts, and I've tried using
varchar and char for the state column, as well as int4, integer, and int
for the other columns.
-Scott
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, January 20, 2006 11:39 AM
To: Scott Schulthess
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Returning setof records
"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
"Scott Schulthess" <scott@topozone.com> writes:
> :( I realize it often automatically converts, and I've tried using
> varchar and char for the state column, as well as int4, integer, and int
> for the other columns.
[ looks again... ] Oh, wait, you're getting caught by a standard
plpgsql gotcha:
> create function placenamesearch(place text, state integer, county text,
^^^^^
> FOR myview IN SELECT featurename, countyname, state, featuretype,
^^^^^
plpgsql variable names win out over table column names, so what's coming
back in the SELECT result's third column is the integer "state"
parameter.
regards, tom lane