telegram_fdw
is a Telegram BOT implemented using the PostgreSQL
foreign data wrapper interface.
telegram_fdw
was tested under PostgreSQL versions 9.4 and 9.5.0
(Multicorn actually has a bug on PostgreSQL 9.5.1)
To install telegram_fdw
on Debian is easy, just follow the steps:
sudo apt-get install postgresql-9.4-python-multicorn
sudo pip install twx.botapi
sudo pip install http://github.com/guedes/telegram_fdw/archive/v0.1.0.zip
The example bellow shows how to use the extension on database monitoring
that owner is the user monitor
.
First, creating the multicorn extension:
psql -d monitoring -U postgres
create extension multicorn;
grant usage on foreign data wrapper multicorn to monitor;
Then, create the multicorn server on monitoring
database:
psql -d monitoring -U monitor
create server multicorn_srv
foreign data wrapper multicorn
options (wrapper 'telegram_fdw.TelegramForeignDataWrapper');
To create a bot, just create a foreign table, like:
create foreign table telegram_monitor_bot
(
update_id bigint,
chat_id bigint,
message varchar,
payload json
)
server multicorn_srv
options (primary_key 'update_id', bot_id '<<THE BOT TOKEN>>');
NOTE: See how BotFather can give you THE BOT TOKEN
In the Telegram app, search your bot (created in BotFather) and send it a messagem.
You can get all messages sent to your bot with a simple SELECT
:
SELECT * from telegram_monitor_bot;
update_id | chat_id | message | payload
----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
58493114 | 11123574 | Hi Bot!! | {"update": {"message": {"delete_chat_photo": null, "new_chat_photo": null, "text": "Hi Bot!!", "sticker": null, "video": null, "chat": {"id": 1123574, "type": "private", "title": null, "username": "testuser", "first_name": "John", "last_name": "Smith"}, "group_chat_created": null, "forward_date": null, "left_chat_participant": null, "location": null, "photo": null, "document": null, "forward_from": null, "new_chat_participant": null, "date": 145210377, "audio": null, "reply_to_message": null, "sender": {"id": 11123574, "first_name": "John", "last_name": "Smith", "username": "testuser"}, "message_id": 101, "caption": null, "contact": null, "voice": null, "new_chat_title": null}, "update_id": 58493114}}
(1 row)
To list all current chats:
SELECT DISTINCT
payload #>> '{update,message,chat,id}' as chat_id,
payload #>> '{update,message,chat,first_name}' as first_name,
payload #>> '{update,message,chat,last_name}' as last_name,
payload #>> '{update,message,chat,username}' as username
FROM telegram_monitor_bot;
chat_id | first_name | last_name | username
----------- ------------ ----------- ----------
11123574 | John | Smith | testuser
11329588 | Foo | Bar | foobar
(1 row)
NOTE:
payload
returns ajson
field so we can use json functions to get the values.
Your bot can respond a message to a chat, using the respective chat_id
. From the example above let's respond to Foo Bar:
INSERT INTO telegram_monitor_bot (chat_id, message)
VALUES (11329588, 'Hi! How are you?');
What about send a little 🐘 ?
INSERT INTO telegram_monitor_bot (chat_id, message)
VALUES (11329588, E'Hi! \U0001f418');
TIP: Emoji Unicode is a Telegram Bot that receives an emoji and returns the respective UNICODE.
That's it! Have fun!
- a BG Worker to async communication,
- functions to help creating bots, send messages, etc,
- a MonitorBot as an example of using KeyboardMarkup,
- translate :emojis: to respective UNICODE
- ...
telegram_fdw
was created by Dickson S. Guedes and is released under PostgreSQL License.
See LICENSE file for information.