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.

1. Download/Install EDB Postgres 9.6

2. As a root user clone the plpgsql_check repository from the Github

    git clone https://github.com/okbob/plpgsql_check.git
   

3. Change to plpgsql_check directory

4. 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*
   

5. Set EDB Postgres 9.6,pg_config in PATH before compiling.

    export PATH=/opt/edb/as9.6/bin:$PATH
   

6. Follow the source compilation steps

     make USE_PGXS=1 clean
     make USE_PGXS=1 all
     make USE_PGXS=1 install
   

7. 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.