Thursday, June 24, 2010

Setting up PL/PgSQL

One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions is PL/pgSQL, because it comes with PostgreSQL and is easy to set up.

PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions etc.. for more details in this blog we are going to learn how adding PL/PgSQL to your Database

Installing PL/pgSQL to Database


To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command as postgres superuser

$ createlang plpgsql database_name
or
cd /usr/local/pgsql/bin/
$ createlang plpgsql -U postgrers_super_uesrname database_name

To add a language, you must have superuser access to the database. If you've just installed PostgreSQL, then you can likely use the default postgres user by passing -U postgres to createlang. From this point, you should be able write and execute function in your pg client.
NOTE : above we just added PL/PgSQL to particular database only, not for entire server.

To create function in postgres can go here
Post a Comment