Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Jim C. Nasby wrote:

> > This may be a better approach.   I've personally never been comfortable with
> > the use of variables outside of SPs and packages; it seems orthagonal to the
> > declaritive nature of SQL.  However, this is a aesthic thing and not really
> > based on practical considerations.
>
> My only comment is I find Oracle's method of having to define a variable
> in sql*plus, call your procedure with it, then print the variable, to be
> a pain.

I agree that it is a bit cumbersome. Any suggestions on how we could
improve on this?

>
> One other point I haven't seen brought up: I find Oracle's concept of
> packages (and more importantly, private variables, procedures,
> functions, etc.) to be extremely useful. It makes it much easier to cut
> your code into blocks when you can define internal-only functions and
> procedures and not worry about others calling them. It also makes a very
> logical way to group code (although schemas in PostgreSQL serve a

I agree that packages give us something like classes in that we can define
related functions/procs into a single namespace. They provide other
features like package level variables and public/private functionality. I
think they major use is namespacing, however, and we can more or less have
that for free with schemas.

> similar purpose when it comes to grouping). Likewise, I find PL/SQL's
> support of defining a procedure or function within a function to be
> useful for grouping code logically. For example:
>
> CREATE OR REPLACE PACKAGE BODY rrd_p AS
>     PROCEDURE update_rrd_buckets
>     AS
>
>         FUNCTION max_end_time_to_delete (
>             rrd_id              rrd.rrd_id%TYPE
>         ) RETURN TIMESTAMP WITH TIME ZONE
>         AS
>         BEGIN
>             ...
>         END;

Again, I can see some possibly advantages but I don't think we will see it
in a first generation implementation of procedures :-).

Gavin