Re: what we need to use postgresql in the enterprise - Mailing list pgsql-general

From: <Bob.Henkel@hartfordlife.com>
> First I would be happy to help get these things in postgresql.  I'm not a
> c/c++ programming guru and would have to learn a bit before I could do the
> coding.  I would be happy to test or talk about what's needed or anything
> like that.  Or just keep the fire burning on these issues that I think are
> keeping postgresql from being the database to use for almost everything.

I am in the "keep the fire burning" camp, though if I had the need and the
financing, I would add features as I needed them.

> I love Postgresql and hope
> my viewpoint coming from Oracle can help improve things.

I think that this is a perspective much valued on the list.  I have no
Oracle experience, but it is still the tarket to beat.
> I also think pl/pgsql is a better choice for stored procedures in general
> depending on the goal of the procedure.  If the procedure is working with
> the database pl/pgsql seems to be the choice.  I would rather use pl/pgsql
> and not have some perl and some python or some other language in my stored
> procedures. This may be more my opinion then the best way of doing things.
> But I like to keep things simple for any future person going to maintain
> the system.

In general I am inclined to agree.  However, there are a few exceptions
(mostly with untrusted languages, such as Pl/PerlU).  They include:
1:  The ability to access the system outside the database.  For example,
writing a logging function in PL/Perlu would allow you to log to a file, and
when this is logged, it will happen regardless of whether the transaction
commits or aborts (i.e. occurs outside the space of the normal transaction).
This could be used in creating a database application error log separate
from the PostgreSQL log.
2:  The ability to do text processing or other advanced features not easily
done in PLPGSQL.
3:  The ability to use network services, such as email, jabber, etc.

Note that you can do all these things in C functions as well, but I would
rather write it in Perl than C :-)

>
> I can see where you are coming from if you haven't used Oracle's exception
> handling.   Here is a snippet of an exception handling block in one of my
> stored procedures.  As you can see I don't need to check for errors after
> each piece of code.  The exception block handles all exception handling.
I
> would say it's very clean and handles errors very well.  this is a simple
> example but you can get the point.
>
> BEGIN
>
> code logic here
> code logic here
> code logic here
> code logic here
> code logic here
>
> EXCEPTION
> /*  Not all of the non nullable fields passed had values  */
>    WHEN e_mandatory_fields_null THEN
>       r_return_cd   := pkg_0100.g_return_missing_fields;
>       pkg_0100.sp_get_error(r_return_cd,r_return_type,r_return_msg);
> /*  Default error code called for all other errors  */
>    WHEN others THEN
>       pkg_0099.g_sql_code := SQLCODE;
>       pkg_0099.g_sql_error_msg := SQLERRM;
>       r_return_cd   := pkg_0001.g_return_failure;
>       pkg_0001.sp_log_error(r_return_cd,r_return_type,r_return_msg);
>
> END;
>
>
Ok.  I understand.  This is extremely useful.  I agree that this should be
done.  Is it on the ToDo?

Best Wishes,
Chris Travers