Data Management in Bioinformatics/Add Programming Language to PostgreSQL

From Wikibooks, open books for an open world
Jump to: navigation, search

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

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