Published on Tue, Apr 10, 2018
plpgsql_check extension helps developers to validate all embeded SQL and SQL statements inside plpgsql function. Its one of the useful extensions particularly when working with plpgsql development. For more details refer to plpgsql_check documentation.
By default, plpgsql_check extension not enabled in community PostgreSQL or commercial EDB Postgres. You need compile the extension with your flavor database. Community PostgreSQL compilation is easy and documented in the above reference link, however below steps help you to compile with commercial EDB Postgres database.
Download/Install EDB Postgres 9.6
As a root user clone the plpgsql_check repository from the Github
git clone https://github.com/okbob/plpgsql_check.git
Change to plpgsql_check directory
Before compiling, we need to make sure we have installed libicu-devel packages. Latest version PostgreSQL/EDB Postgres binaries are linked to a particular libicu to support International Componenets for Unicode(ICU). In case, if libicu-devel package not installed on your machine then you may encounter below error:
In file included from /opt/edb/as9.6/include/server/tsearch/ts_locale.h:18:0,
from plpgsql_check.c:71:
/opt/edb/as9.6/include/server/utils/pg_locale.h:54:26: fatal error: unicode/ucol.h: No such file or directory
#include <unicode/ucol.h>
Use YUM to install libicu-devel package.
yum install libicu*
Set EDB Postgres 9.6,pg_config in PATH before compiling.
export PATH=/opt/edb/as9.6/bin:$PATH
Follow the source compilation steps
make USE_PGXS=1 clean
make USE_PGXS=1 all
make USE_PGXS=1 install
Switch as “enterprisedb” user, connect to database and create extension
su - enterprisedb
psql -U enterprisedb -d edb -p 5444
psql.bin (9.6.5.10)
Type "help" for help.
edb=# create extension plpgsql_check;
CREATE EXTENSION
Now, you can play with plpgsql_check extesion.
I have recorded my terminal while compile plpgsql_check extension. You can watch the terminal recording here
Thank you for visiting the site. Enjoy.