Returning setof records

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux