Setting up extra extensions / features on proprietary platform like Windows is a challenge because we don’t have a default build environment. Setting up a complete build environment for installing a language extension is bigger challenge. Thanks to vibrant community, many of the heavy lifting is already done. I would like to Acknowledge the contribution Dave Cramer who built the Windows extension binaries and Shawn from BigSQL user forum for leading me to this learning. Special thanks to Joe Conway and PL/R community for maintaining the language extension.
2. getting pl/R extension binaries
Windows binaries for PL/R Repository is available here
As the name indicating, PostgreSQL 9.6 extension is built against R version 3.4.1 and extension for Postgres 10 is built against R version 3.4.2. Download the corresponding zip file and unpack into a direcotory. For this demonstration I am using Postgres 9.6 extension. Downloaded plr-18.104.22.168-pg9.6-R3.4.1-win64.zip file and unpacked into a directory plr-22.214.171.124-pg9.6-R3.4.1-win64
3. Installion of R
As the name indicates, this needs R version 3.4.1. Windows installer for the same is available here One important aspect I would suggest to take care is to avoid directory names with spaces while installing R or Postgres. In the installer screen, I Installed into a directory C:\R\R-3.4.1 instead of “Program Files” to avoid spaces and potential problems that can cause.
We can leave rest of defaults in the Installer.
4. Install Postgres if not already done.
For this demonstration, I am using portable PostgreSQL binaries provided by BigSQL project. its just a matter of opening windows command line in administrator mode and run a single line power shell script.
5. Installing PL/R Extension
We have to do this manually, by copying the files to corresponding directories. In order to detect the location of files, few environment variables also required. I have done the following from the command prompt (from the directory where PL/R extensions are unpacked)
setx PGDIR %PGDIR%
setx R_HOME %R_HOME%
setx PATH %PATH%
setx PGDIR %PGDIR% /m
setx R_HOME %R_HOME% /m
setx PATH %PATH% /m
p>copy plr.dll %PGDIR%\lib\postgresql\
copy plr.sql %PGDIR%\share\postgresql\extension\
copy plr.control %PGDIR%\share\postgresql\extension\
copy plr–126.96.36.199.sql %PGDIR%\share\postgresql\extension\
copy plr–unpackaged–188.8.131.52.sql %PGDIR%\share\postgresql\extension\ `
6. Restart Postgres and create extension
From the Postgres installation directory, issued following to restart postgres.
./pgc restart pg96
By doing this, all the environment varables will be taken into effect and postgres process knows where to look for libraries.
Now we can create pl/R extension by connecting to psql
postgres=# create extension plr;
7. Test and verify whether extension is functioning
List of installed extensions Name | Version | Schema | Description ---------+----------+------------+----------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plr | 184.108.40.206 | public | load R interpreter and execute R script from within a database
Some usefull plr functions for testing
postgres=# SELECT * FROM plr_environ();
postgres=# select plr_version ();