PostgreSQL Administration - Returning setof records

Author

Returning setof records

Scott Schulthess

2006-01-20, 11:24 am

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(plac
e 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('Che
shire',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

Tom Lane

2006-01-20, 11:24 am

"Scott Schulthess" <scott@topozone.com> writes:
> select * from placenamesearch('Che
shire',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

Scott Schulthess

2006-01-20, 11:24 am

Tom -

I've also tried this select * from placenamesearch('Che
shire',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('Che
shire',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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2006-01-20, 11:24 am

"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(plac
e 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links