bryanthowell-tableau / postgresql-plpython-webservice Goto Github PK

Hello,

When I run the code, it show that the create function had worked. However I am not getting data back from the request. I did change the plpython2u to plpython3u, but i don't think that caused he problem.

I would think that this is a simple call, as it is going to https://services.nvd.nist.gov/rest/json/cves/2.0
and for my test, I only grabbed a handful of the header
{"resultsPerPage": 2000, "totalResults": 226528, "format": "NVD_CVE", "version": "2.0"}

This is new to me, so it might be something on my end, but I would appreeciate your help.

Thank you

Full code
`CREATE OR REPLACE FUNCTION web_service(format text, resultsPerPage int, version text, totalResults int)
RETURNS TABLE(like mytable1)
AS $$

for arg in [format, resultsPerPage, version, totalResults]:
if arg == "" or arg is None:
return []

import requests
import datetime
storage_table_name = 'mytable1'

rest_endpoint = 'https://services.nvd.nist.gov/rest/json/cves/2.0?hasKev'.format(series_name)
unique_request_id = "{}{}{}_{}".format(format, resultsPerPage, version, totalResults)
if response.status_code >= 400:
return []
rest_json = response.json()

insert_query = "INSERT INTO {} VALUES(NOW(), $1, $2, $3, $4, $5, $6, $7, $8)".format(storage_table_name)

insert_types = ["text", "int", "text", "int", "text", "text", "text", "text"]

plan = plpy.prepare(insert_query, insert_types)

for first_level in rest_json:
	data_in_first_level = rest_json[first_level]
for second_level in data_in_first_level['data']:
	insert_list = [unique_request_id, ]
	insert_list.extend(second_level)
	plpy.execute(plan, insert_list)

return_query = """
SELECT * FROM
{}
WHERE unique_request_id = '{}'
""".format(storage_table_name, unique_request_id)

return plpy.execute(return_query)

$$ LANGUAGE plpython3u;`