Thread: Languages and Functions

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

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

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

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

To see how PG does it:
   psql -E
   \df

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

1. It's more likely to be available (not relevant if you set up the
system yourself).
2. It's probably more tested (because it's more available, not through
any failing of the other languages)
3. I'd guess for a small function, called for the first time in this
session you'd avoid any startup costs for the perl interpreter
4. It uses the same code as the SQL engine to handle expressions, so
there's no conflict between understanding of how floating-point or
timestamps should behave in corner cases.

Against it is the fact that it's statically typed and fairly inelegant.

--
   Richard Huxton
   Archonet Ltd

On May 29, 2007, at 10:30 , Michael Glaesemann wrote:

> 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

Ha! That link won't be very helpful, will it :)

This one's better:

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-proc.html

And Richard's suggestion of using -E in psql is definitely an easy
way learn how to reference the system catalogs.

Michael Glaesemann
grzm seespotcode net

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

    Yes, if you want to loop over large amounts of data (FOR row IN SELECT)
plpgsql will be faster since it does not have to convert the data from
postgres to python/perl format.