Data Management in Bioinformatics/Add Programming Language to PostgreSQL

Examples of programming language in PostgreSQL edit

Function example in Python

create or replace function fun1(n integer) returns integer as $$
return n * 100
$$ language plpythonu;

Function example in pgSQL (equivalent of PL/SQL in postgres).

create or replace function fun2(n integer) returns integer as '
   return n * 200;
' language plpgsql;

Checking for language support edit

SQL query:

SELECT lanname as "Name", (CASE WHEN lanpltrusted THEN 'yes' ELSE 'no' END) as "Trusted?" 
FROM pg_catalog.pg_language 
WHERE lanispl;


createlang utility [params: -d DBNAMES, -U USER, -l is for listing]

C:\Program Files\PostgreSQL\8.3\bin> createlang -e -l -d postgres -U postgres


 Procedural Languages
   Name    | Trusted?
 plpgsql   | yes
 plpythonu | no

Adding language support edit

createlang utility [params: -U USER LANG DBNAME] *requires superuser access to database

C:\Program Files\PostgreSQL\8.3\bin>createlang -e -U postgres plpgsql postgres
C:\Program Files\PostgreSQL\8.3\bin>createlang -e -U postgres plpythonu postgres

Adding C/C++ support may be more involved as it requires compiling the source into ".so" on the server. More details here PostgreSQL: Server Programming