Re: Languages and Functions

On May 29, 2007, at 9:49 , Robert James wrote:

> 1. How can I get a list of available functions (ie, user defined or
> contrib) using SQL?

You can take a look in the pg_proc table, which is part of the system
catalog, to see which functions are installed.

file:///usr/local/pgsql/pgsql-8.2.0/doc/html/catalog-pg-proc.html

You might try something like

SELECT proname
        , pronargs
        , lanname
FROM pg_proc
NATURAL JOIN (
         SELECT oid as prolang, lanname
         FROM pg_language) AS lang
ORDER BY proname, pronargs, lanname;
               proname               | pronargs | lanname
------------------------------------+----------+----------
RI_FKey_cascade_del                |        0 | internal
RI_FKey_cascade_upd                |        0 | internal
RI_FKey_check_ins                  |        0 | internal
RI_FKey_check_upd                  |        0 | internal
...


> 2. Is there any performance or other advantage to using PL/pgsql
> over Pl/Perl or Python?

It depends on what your function is doing. If you're doing simple SQL-
type things, PL/pgsql might be a good fit. If you're doing more
advanced text processing or calling external libraries, PL/Perl could
be better. I don't have any experience with Python.

As with all things, test and benchmark to for your particular case
for the best results :)

Hope this helps.

Michael Glaesemann
grzm seespotcode net