plphyton function - return each list value as a row ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

plphyton function - return each list value as a row ?

Karsten-3-2
Hi All,
 
I am trying to create my first plphyton function ( retrieving earthquake
data from an API) .
Overall I was able to get one single row to be returned, but am struggling
how to return the entire list I have as multiple rows - see below. Currently
I get the following error when running the GetEartquakeAll function:

select GetEartquakeAll('2020-01-01' ,'2020-03-01', -120, 40,200, 1.7)
gives me
ERROR: length of returned sequence did not match number of columns in row

How can I 'simply' return each list value as a row ?
Thanks
Karsten Vennemann
 
CREATE OR REPLACE FUNCTION GetEartquakeAll(start date ,stop date, lon float,
lat float,radius int, minmagnitude float) RETURNS equake_values AS $$
  import urllib2
  import json as json
  data =
urllib2.urlopen('https://earthquake.usgs.gov/fdsnws/event/1/query?format=geo
json&starttime=%s&endtime=%s&latitude=%s&longitude=%s&maxradiuskm=%s&minmagn
itude=%s&orderby=magnitude' % (start,stop,lat,lon,radius,minmagnitude))
  js_data = json.load(data)
  equake = js_data
  equakearray = []
  a = 0
  for i in equake['features']:
    equakeplace = i['properties']['place'] # tile for earthquake location
    magnitude =   i['properties']['mag']
    qlong =       i['geometry']['coordinates'][0]
    qlat =        i['geometry']['coordinates'][1]      
    equakevalue = {"place": equakeplace, "magnitude": magnitude , "qlong":
qlong, "qlat": qlat}
    equakearray.append(equakevalue)
    a = a+1
  return equakearray
$$ LANGUAGE plpythonu;
           
# create custom data type that is returned from equake  data API query
CREATE TYPE equake_values AS (
  place text,
  magnitude float,
  qlong  float,
  qlat  float
);  




Reply | Threaded
Open this post in threaded view
|

Re: plphyton function - return each list value as a row ?

David G Johnston
On Saturday, July 25, 2020, karsten <[hidden email]> wrote:
Hi All,

I am trying to create my first plphyton function ( retrieving earthquake
data from an API) .
Overall I was able to get one single row to be returned, but am struggling
how to return the entire list I have as multiple rows - see below. Currently
I get the following error when running the GetEartquakeAll function:

select GetEartquakeAll('2020-01-01' ,'2020-03-01', -120, 40,200, 1.7)
gives me
ERROR: length of returned sequence did not match number of columns in row

How can I 'simply' return each list value as a row ?
Thanks
Karsten Vennemann

CREATE OR REPLACE FUNCTION GetEartquakeAll(start date ,stop date, lon float,
lat float,radius int, minmagnitude float) RETURNS equake_values AS $$

The choice of mailing list for this is unusual as this seems to have nothing to do with general SQL.

Anyway, you may find the following documentation section useful.


David J.