Stored procedure proposal

Proposal: adding new database objects - stored procedures

---

STEP 1

Properties:

• non atomic
• allows explicit transaction control (per SQL/PSM)
• cannot be used from SELECT statement (use statement CALL)
• allows getting references to INOUT, OUT parameters
• IN parameters can be expression, but if all arguments are variables we can use FASTCALL
• allows default values (simplify maintainance procedures)
• allow using RETURN value statement (returned value is readable via GET DIAGNOSTICS var = RETURN_STATUS (SQL/PSM)

Why:

• conformance with SQL/PSM
• simplify porting stored procedures from others databases
• allows solving some task, which need ref to variables (variable binding, etc)
• allows choosing right function with specified result type (currently we can specify only IN and INOUT args)
• allows faster calling stored rutines (FASTCALL)
• allows using VACUUM in stored procedure

If host environment can put Datum references, then H.E. can call procedures with INOUT and OUT args. If not, then it can only call procedures with IN params.

How:

• own simple executor

STEP 2

Properties:

• allow unbounded queries
• allow multirecordset output
• every recordset is sended immediatly (procedures aren't atomic, higher interactivity, less memmory usage)

Why:

• allow writing complex analytic or administration procedures with non regular output
• simplify porting stored procedures from Sybase based DBMS (Microsoft SQL server, MySQL)
• allow strategy: everywhere is stored procedure

Case:

  BEGIN TRANSACTION
  -- long time data update operation 
  SELECT * FROM ... -- test output etap 1
  -- long time data update operation 
  SELECT * FROM ... -- test output etap 2
  IF NOT force THEN
    ROLLBACK
  ELSE
    COMMIT
  ENDIF

Previous proposals:

• http://archives.postgresql.org/pgsql-hackers/2004-09/msg00412.php Neil Conway (only syntactic changest for plpgsql, drop PERFORM statement)
• http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php Gavin Sherry
• http://archives.postgresql.org/pgsql-hackers/2005-02/msg00783.php Tom Lane reply on previous proposal (result is OUT params for functions)
• http://archives.postgresql.org/pgsql-hackers/2005-11/msg01015.php Martijn van Oosterhout (Returning multiple result sets)

What we currently cannot:

• transaction control
• bind variables

What we can, ugly (more code, less readable, ugly solution)

• choose function with selected returned type (with specific function name)
• multirecord set (setof refcursor .. not intuitive, missing support in psql)
• default values

What impact of missing feature: bigger preferences for MySQL 5.x (current support of stored procedures is basic, it isn't top topic for mysql and mysql users too, but 3 months work and they had similar sql/psm implementation like plpgpsm with better SQL/PSM support and with all missing features. Currently they have more then one comparable data engines.