Data Management in Bioinformatics/Add Programming Language to PostgreSQL

Examples of programming language in PostgreSQL

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 '
begin
   return n * 200;
end;
' language plpgsql;

Checking for language support

SQL query:

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

OR

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

Result:

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

Adding language support

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