Re: SQL-Invoked Procedures for 8.1

On Thu, Sep 23, 2004 at 11:12:18AM -0700, Josh Berkus wrote:
> A second point, which I brought up with you on IRC, is to eliminate
> overloading and allow named parameter calls on SPs. This is extremely
> useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's
> essential for any operation that wants to create an SP-centric middleware as
> only named parameter calls allow developers to add parameters to existing
> procedures without breaking existing calls.
>
> For anyone who doesn't know what I'm talking about, it's this form:
>
> CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT )
> etc.
>
> Where you can:
> CALL do_some_work( alpha = 5, beta = 7 )
> .. and then gamma picks up its default, if any, or even:
> CALL do_some_work( gamma = 'Lord Fontleroy', beta = 7, alpha = 1 )
>
> The idea being that for SPs, schema.name is unique regardless of the
> parameters. Even if implementing named parameter calls is beyond the
> current spec, I will argue strongly in favor of eliminating overloading for
> SPs. Overloading serves no purpose for them and prohibits the whole concept
> of default values.

Since plpgsql seems closer to PL/SQL than TSQL, I'd suggest using the
PL/SQL convention of CALL some_proc( alpha => 'a', bravo => 'b'). Also,
I agree that having defaults is much more useful than overloading when
it comes to creating optional parameters, but I think allowing for
type-overloaded stored procedures is also useful. Or perhaps allowing
for the definiton of a generic input type and a means to tell what
datatype was actually passed in.

> 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.

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
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;
BEGIN
...
DELETE FROM table WHERE ts <= max_end_time_to_delete(v_rrd_id);
...
END;
END;

Though, I would prefer if you could define internal procedures/functions
*after* the main code; I think it would greatly improve readability.

I'm not suggesting you try and implement these features now, but you
might want to consider what impact they might have on your design.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"