Re: PL/pgSQL TODO

Josh Berkus wrote:
> Bruce,
>
> > OK, found it, attached, from April 17.  It is still sitting in my
> > "review" email box to be summarized into the TODO list.  In fact, I
> > have
> > ~30 emails from that thread that i have to re-read and summarize.
>
> There should be a follow-up e-mail from me listing 4-5 more items.  If
> you can't find it, I will.

Yep, got that one too, attached.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
From pgsql-patches-owner+M3724@postgresql.org Thu Apr 18 08:28:59 2002
Return-path: <pgsql-patches-owner+M3724@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
    by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3ICSwS21973
    for <pgman@candle.pha.pa.us>; Thu, 18 Apr 2002 08:28:58 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
    by postgresql.org (Postfix) with SMTP
    id AE09F47626E; Thu, 18 Apr 2002 08:19:00 -0400 (EDT)
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
    by postgresql.org (Postfix) with ESMTP id B492C4763E9
    for <pgsql-patches@postgresql.org>; Wed, 17 Apr 2002 12:08:55 -0400 (EDT)
Received: from [63.195.55.98] (account <josh@agliodbs.com>)
  by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9)
  with HTTP id 1374310; Wed, 17 Apr 2002 09:08:58 -0700
From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: [PATCHES] [SQL] 16 parameter limit
To: John Proctor <jproctor@prium.net>,
   Neil Conway <nconway@klamath.dyndns.org>
cc: josh@agliodbs.com, peter_e@gmx.net, pgman@candle.pha.pa.us,
   tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org
X-Mailer: CommuniGate Pro Web Mailer v.3.5.9
Date: Wed, 17 Apr 2002 09:08:58 -0700
Message-ID: <web-1374310@davinci.ethosmedia.com>
In-Reply-To: <200204170619.g3H6JKa29940@slxmail01.prium.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
Status: OR

Folks,

> 1) More than 16 parameters. � This can be parameter configurable if 
> necessary, but up to 128 would cover 99.9%.
> 
> 2) Better exception handling. �The procedure should be able to trap
> any data 
> related exception and decide what to do.   No function should ever
> abort.  It should raise a trappable exception and let me decide what
> to do.
> 
> 3) Allow transactions inside of functions. � Mostly for incremental
> commits. 
> Each transaction shoud be implicitely started after any CrUD
> statement and 
> continue until a commit or rollback.
> 
> 4) Allow autonomous transactions. �This is related to number 2. �In
> Oracle, I 
> can track every single exception and log it in a central table with
> details, 
> even if I rollback the current transaction or savepoint. � This is a
> must for 
> tracking every single database error in an application at the exact
> point of 
> failure.
> 
> 5) Find a way to get rid of the requirement to quote the entire proc.
> � This 
> is very clumsy. � The PL/pgSQL interpreter should be able to do the
> quoting 
> and escape what it needs.
> 
> 6) Allow function parameters to be specified by name and type during
> the definition. Even aliasing is cumbersome and error prone on large
> procs, especially during development when changes are frequent.
> 
> 7) Allow function parameters to be passed by name, not just
> positional. �i.e. 
> get_employee_salary(emp_id => 12345, tax_year => 2001).
> 
> 8) Add packages. �This is a great way to group related functions,
> create 
> reusable objects, like cursors, etc.
> 
> 9) Allow anonymous PL/pgSQL blocks. � It should not be required to
> create a 
> function for every PL/pgSQL block. � Often, I just want to do
> something quick 
> and dirty or write complex blocks that I don't even want saved in the
> 
> database. �I can just keep then in a file and execute when necessary.

Also:

10) Allow declaration of all PostgreSQL data types, including custom
data types and domains, inside functions.  Especially important are
Arrays, which are supported as parameters but not as declarations.

11) PL/pgSQL has functionality 100% analagous to cursors, with a
different syntax.  While the PL/pgSQL record loop is easier to use, the
lack of support for standard cursor syntax mars the poratbility of
Oracle procedures to Postgres and vice-versa.

12) The biggie:  Allowing the easy return of query results from a
procedure.  This is currently supported through a rather difficult
workaround involving either the ROWTYPE datatype or a return Cursor.
 Both approaches require the use of a procedural code loop on the
interface side to read the data being returned ... much clumsier than
just dumping the data ala PL/SQL or T-SQL.  If implemented, this rowset
return would the the difference between a CREATE FUNCTION and a CREATE
PROCEDURE statement.

13) Allow the creation of multiple output parameters for PROCEDURES (as
opposed to FUNCTIONS) in the parameter declaration.

14) Procedures should have their own permissions, which supercede the
permissions on the tables being affected if the procedure is created by
the database owner, in the same way that Views can allow users to
Select data they would not be entitled to from the base tables.  In
other words, if I declare "GRANT SELECT ON fn_modify_assignment TO
phpaccess", the user phpaccess should be able to run
fn_modify_assignment even if that user has no permissions on the
assignment table itself.

-Josh Berkus

P.S. I haven't brought up these issues before because there is no way I
can contribute any significant resources to completing them.  

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)