PostgreSQL Discussion - Stored Procedure: PL/Perl or PL/SQL?

Author

Stored Procedure: PL/Perl or PL/SQL?

Joshua Kramer

2005-12-29, 8:23 pm

Greetings all,

I'm working to integrate an accounting system
(http://www.linuxcanada.com) with another application. Fortunately,
both use PG, so integration should be easy.

I want to be able to insert invoices, and invoice line items, into the
accounting system. As you might expect, this requires many selects and
inserts involving serveral tables wrapped around a transaction. As I
see it, there are a couple of ways to do this:

1. Attach a trigger which runs a Stored Procedure in PL/SQL;

2. Create a Perl Module that connects to a database via DBI and does the
work; the trigger would then be written in PL/Perl, and would use the
Perl Module to do the work.

The advantage to #2 is that I'd have invoice migration and a
general-purpose module for inserting invoices, with the same amount of
work that I'd have for just invoice migration using PL/SQL. The
drawback is the overhead of using Perl inside PG; how much overhead is
there?

What else should I consider?

Thanks,
-Josh

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sean Davis

2005-12-29, 8:23 pm

On 12/29/05 4:10 PM, "Joshua Kramer" <josh@bitbuckets.com> wrote:

>
> Greetings all,
>
> I'm working to integrate an accounting system
> (http://www.linuxcanada.com) with another application. Fortunately,
> both use PG, so integration should be easy.
>
> I want to be able to insert invoices, and invoice line items, into the
> accounting system. As you might expect, this requires many selects and
> inserts involving serveral tables wrapped around a transaction. As I
> see it, there are a couple of ways to do this:
>
> 1. Attach a trigger which runs a Stored Procedure in PL/SQL;
>
> 2. Create a Perl Module that connects to a database via DBI and does the
> work; the trigger would then be written in PL/Perl, and would use the
> Perl Module to do the work.
>
> The advantage to #2 is that I'd have invoice migration and a
> general-purpose module for inserting invoices, with the same amount of
> work that I'd have for just invoice migration using PL/SQL. The
> drawback is the overhead of using Perl inside PG; how much overhead is
> there?
>
> What else should I consider?

There have been a few discussions about this in the recent past (look in the
archives) and the general consensus is that one should use the language that
is most comfortable (known). After that, use the language that is best
suited to the task. Array manipulations in PL/PgSQL are given as one
example of where pl/perl might be easier and faster. As for overhead, there
isn't any more for pl/perl than for pl/pgsql, from what I understand. One
final point if you are thinking of writing a perl module for use in pl/perl,
the function will have to be run as untrusted, I think. This could have
changed recently, but I'm not aware of it.

So, choose whichever is easiest for you for the job. If performance is the
ONLY issue, then testing under real conditions is probably the only way to
be sure that what you are doing is the right way.

Sean

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links