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

1 comment:

Anonymous said...

Hi, i thiոk that i sаw you visited my website thus i came
to “return thhe favor”.I ɑm attempting tο find thingѕ tо
enhance my site!І suppose its oҝ to usе some ߋf your ideas!!

Αlso visit mƴ blog ... SEO (