PostgreSQL applications at times need to communicate with external servers using FTP. Oracle has the UTL_TCP package that is very useful for PL/SQL applications. PostgreSQL doesn’t have an equivalent package, but it does have something really powerful … the ability to write functions in various scripting languages. PostgreSQL has four languages that are part of the core distribution: PL/pgSQL, PL/Tcl, PL/Perl, & PL/Python. The example that I give below uses PL/Python to write to an FTP location.
Consider a scenario where you want to run a few PL/pgSQL routines to create daily data files and then place them on an FTP server for your customer. Theoretically, this is possible using external shell scripts, but you could very easily run into synchronization issues between your database’s functions and the scripts in your OS. A very easy way out is to write a small PL/Python function like the one below that gets called once your daily routines are done within the database.
CREATE OR REPLACE FUNCTION put_file_on_ftp (file_name TEXT) RETURNS VOID AS $BODY$ from ftplib import FTP ftp = FTP('ftp.testing.com') ftp.login('user_name', 'password') ftp.cwd('path/to/directory') ftp.storbinary('STOR my_file.bin', open(file_name) $BODY$ LANGUAGE plpythonu VOLATILE COST 100;
Let me explain the body of the above function line by line.
from ftplib import FTP
This line imports the FTP class from ftplib module. FTP class implements the client side of FTP protocol, which can be used in the subsequent steps of your function.
ftp = FTP('ftp.testing.com')
This returns an instance of the FTP class connected to the host listed as parameter.
The instance returned by the FTP class above is being used here to actually login to the server. In case no user name and password are provided, the function would assume an anonymous login. Note that most FTP commands are allowed only after the user has successfully logged in.
Obviously you shouldn’t hard code your username and password in a function, but I am doing it here just to simplify the example.
This line switches to the target directory specified as a parameter.
ftp.storbinary('STOR my_file.bin', open(file_name)
This function stores the file specified in file_name (including complete path on the source server) on the target server as my_file.bin. The file transfer is in binary format. The first argument should always be a valid STOR command.
For more commands that you can use for FTP using PL/Python, please refer to the link: https://docs.python.org/2/library/ftplib.html.
Hope this was helpful!