Compiling PL/Proxy with PostgresPlus Advance Server 9.1

Published on Sat, Jun 2, 2012

PostgresPlus Advance Server 9.1(PPAS) is EnterpriseDB product, which comes with enterprise features as additional with community PostgreSQL. Most of the contrib modules(pgfoundry) can be pluged into this product using Stackbuilder. However,currently Pl/Proxy is not bundled or downloadable with Stack-builder. So,here is how you can compile the Pl/Proxy with PPAS 9.1.

  1. Download Pl/Proxy.

    wget http://pgfoundry.org/frs/download.php/3274/plproxy-2.4.tar.gz tar -xvf plproxy-2.4.tar.gz make PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config make intall PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config

    Note: Flex & Bison must be installed before compiling pl/proxy.

  2. After sucessfull configuration, you get two files, plproxy.so in $PGPATH/lib & plproxy–2.4.0.sql in $PGPATH/share/extention/ location.
    Execute the .sql file which creates call_handler & language.

    bash-4.1$ psql -p 5444 -U enterprisedb -d edb -f /opt/PostgresPlus/9.1AS/share/extension/plproxy–2.4.0.sql CREATE FUNCTION CREATE LANGUAGE CREATE FUNCTION CREATE FOREIGN DATA WRAPPER

Now you can see the language installed.

edb=# \dL
        List of languages
  Name   |    Owner     | Trusted
---------+--------------+---------
 edbspl  | enterprisedb | t
 plpgsql | enterprisedb | t
 plproxy | enterprisedb | f
(3 rows)
  1. Lets test the sample code with pl/proxy.

    create table users(username text,blog text); insert into users values(‘Raghav’,‘raghavt.blogspot.com’);

    CREATE or replace FUNCTION get_user_blog(i_username text) RETURNS SETOF text AS $$ CONNECT ‘dbname=edb’; SELECT blog FROM users WHERE username = $1; $$ LANGUAGE plproxy;

    edb=# select * from get_user_blog(‘Raghav’);

    get_user_blog

    raghavt.blogspot.com (1 rows)

All set to go testing with pl/proxy on PPAS 9.1. If you want to know how to setup pl/proxy, follow below links.
http://www.depesz.com/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/

–Raghav