A PostgreSQL FDW for Open Weather Map
- This FDW just supports one city query.
- The default appid in the code is the sample appid which can not query dynamically. (static data results)
- You need to apply your own appid on Open Weather Map.
This FDW is based on Multicorn project.
# apt install postgresql-12-python3-multicorn
# git clone https://github.com/ycku/owmfdw.git
# cd owmfdw
# cp owmfdw.py /usr/lib/python3/dist-packages/multicorn/
# apt install postgresql-11-python3-multicorn
# git clone https://github.com/ycku/owmfdw.git
# cd owmfdw
# cp owmfdw.py /usr/lib/python3/dist-packages/multicorn/
- Tested with PostgreSQL 11 and 12
- Replace appid with your appid
CREATE EXTENSION multicorn;
CREATE SERVER owmfdw_srv FOREIGN DATA WRAPPER multicorn OPTIONS ( wrapper 'multicorn.owmfdw.owmfdw'
,appid 'b6907d289e10d714a6e88b30761fae22' );
CREATE FOREIGN TABLE owmfdw ( city text, description text, temp float, lang text ) SERVER owmfdw_srv;
SELECT * FROM owmfdw WHERE city='Sapporo';
SELECT * FROM owmfdw WHERE city='Taipei' and lang='zh_tw';
SELECT * FROM owmfdw WHERE city='Tokyo' and lang='ja';
postgres=# SELECT * FROM owmfdw WHERE city='Sapporo' and lang='ja';
city | description | temp | lang
---------+--------------+------+------
Sapporo | 曇りがち | 10 | ja
(1 row)
You can do it when you want to log the weather.
You are using internal table and foreign table in one query.
CREATE TABLE weatherlog (city TEXT, temp FLOAT, logtime TIMESTAMP);
INSERT INTO weatherlog SELECT city, temp, now() FROM owmfdw WHERE city='Sapporo';
SELECT * FROM weatherlog;
city | temp | logtime
---------+-------+----------------------------
Sapporo | 10.57 | 2019-11-05 14:31:57.884117
(1 row)