pgsql stored procedure - internal, c or sql language is the best?

Question

I have a production pgsql server with the following stored procedure language support:

1. internal
2. c
3. sql

I cannot find examples for internal and c, just pl/pgsql or in rare cases sql. I'll try to get the provider to install other languages, but providers usually not provide, so I don't think this will happen... So I am stucked with these languages...

Which one should I choose and why? (if you have a good tutorial too, then please write it in your answer or in comment)

select * from pg_language

Btw I could not test the c and the internal without tutorial, so maybe there is a simple solution: I cannot use them because they are not trusted.

Edit - after the solution

The create language what worked for me. After that I checked what languages are available with the following query:

select * from pg_pltemplate

You can read more about create language here.

I will use plpgsql, I found a good book about postgresql here: The PostgreSQL Programmer's Guide , Edited by Thomas Lockhart

Answer 1

Typically, you can use four, five PL languages - SQL, PL/pgSQL, PL/Python or PL/Perl, C.

• SQL - short one line functions - can be super fast due inlining (like macro)
• PL/pgSQL - good for business logic implementation (if you like it or not, it can accelerate your application due: less network traffic, less data type conversions, less interprocess communication - PLpgSQL uses types compatible with Postgres and functions are executed in PostgreSQL SQL executor process) - good for code with lot of SQL queries due native support of SQL (you can like it or you can prefer ORM - personally I dislike ORM - it is main performance killer what I know).
• PL/Python or PL/Perl - good for special tasks where PL/pgSQL is not good or miss necessary features - I like PL/Perl due possibility to use CPAN archive in PostgreSQL - need send main or need to do SOAP call - all is in CPAN.
• C - need maximum performance or access to PostgreSQL internals - then use a C functions. A fast implementation of some generic strings, date, math routines are the most simply in C language.

Examples of C codes you can find in

• documentation http://www.postgresql.org/docs/9.2/static/xfunc-c.html
• contrib archive https://github.com/postgres/postgres/tree/master/contrib
• PGXN archive http://pgxn.org/
• pgfoundry archive http://pgfoundry.org/

C language can be used for implementation of own datatypes, necessary operations and index support. You can find lot of PostgreSQL extensions - very famous is PostGIS.

Answer 2

Looking at your listing of pg_language, this shows the default values: if I create a new database using createdb, PostgreSql 8.4/Debian, it's the same output. The listing may already contain another line for PL/pgSQL, depending on the version and/or your data center (pointed out by a_horse_with_no_name).

So you have

• "built-in functions" (internal)
• "Dynamically-loaded C functions" (c)
• "SQL-language functions" (sql)

If you run

CREATE LANGUAGE plpgsql;

there will turn up another line for PL/pgSQL (if you have the privilege).

If you installed PL/Java for example, you would get

• "Java trusted" (java)
• "Java untrusted" (javau)

which show up in the listing as well.

---

Some guidelines as for choosing a language

• If you want a higher level language, consider Scala (requires support for PL/Java or JVM based stored procedures respectively). So you have the functional paradigma not only in SQL, but also in your stored function/procedure. Of course, like Java you have OOP as well.

• If you are using Java, have a look at Java stored procedures (requires PL/Java). As for an example, look here. In contrast to PL/pgSQL, you have full OOP.

PL/Java tends to be difficult to install, so it's not really appreciated by data centers. It's worth the trouble, because you can have the same language both for client/application servers and for stored procedures/functions: There is no need to learn another language. For example, you can access result sets the same way. The only thing that differs is the JDBC URL. In contrast to PL/pgSQL, these stored procedures are portable, if the other database supports JVM based stored procedures as well.

• If you have to choose one from the already available languages, consider PL/pgSQL. It's normally always installed, and you do not have to deal with memory allocations.

• If you have to interface with the operating system/libraries, there is C. To get an impression, look here. It's not really difficult, it's just more boiler-plate around the functionality.

• If you want C++, it gets harder, because the interface between PostgreSql and the C/C++ modules uses the C calling convention, so you should have a C file which sits between PostgreSql and your C++ module. To get an impression, look here.

If you are not using PL/pgSQL, the most difficult part is the installation (PL/Java), and the interfacing code (PL/Java, PL/C, PL/C++). If you have set it up initially, it's really a pleasure to have the language you really want in stored procedures/functions as well. It's worth the trouble.

Answer 3

If you access the database from some software tool (for instance, from java through JDBC) you also develop, it may be better to simplify queries, do more job on the client side and avoid the database side scripting.

The rationale is that these server side scripts are more difficult to test (database is required for unit tests), debug (normally way more complex that for your own code under debugger) and maintain (upgrade, etc). Bugs in server side scripts are often overlooked for a longer time as being separate, these scripts are only infrequently seen by the client side developers.

However if anyway preferred, we have used PL/PSQL in the past as it is possible to have the automated scripts that install all code on the server automatically just through JDBC connection.