SQL-standard function body - Mailing list pgsql-hackers

This adds support for writing CREATE FUNCTION and CREATE PROCEDURE 
statements for language SQL with a function body that conforms to the 
SQL standard and is portable to other implementations.

Instead of the PostgreSQL-specific AS $$ string literal $$ syntax,
this allows writing out the SQL statements making up the body
unquoted, either as a single statement:

      CREATE FUNCTION add(a integer, b integer) RETURNS integer
          LANGUAGE SQL
          RETURN a + b;

or as a block

      CREATE PROCEDURE insert_data(a integer, b integer)
      LANGUAGE SQL
      BEGIN ATOMIC
        INSERT INTO tbl VALUES (a);
        INSERT INTO tbl VALUES (b);
      END;

The function body is parsed at function definition time and stored as
expression nodes in probin.  So at run time, no further parsing is
required.

However, this form does not support polymorphic arguments, because
there is no more parse analysis done at call time.

Dependencies between the function and the objects it uses are fully
tracked.

A new RETURN statement is introduced.  This can only be used inside
function bodies.  Internally, it is treated much like a SELECT
statement.

psql needs some new intelligence to keep track of function body
boundaries so that it doesn't send off statements when it sees
semicolons that are inside a function body.

Also, per SQL standard, LANGUAGE SQL is the default, so it does not
need to be specified anymore.

Note: Some parts of the patch look better under git diff -w (ignoring 
whitespace changes) because if/else blocks were introduced around 
existing code.

TODOs and discussion points:

- pg_dump is not yet supported.  As a consequence, the pg_upgrade
tests don't pass yet.  I'm thinking about changing pg_dump to use 
pg_get_functiondef here instead of coding everything by hand.  Some 
initial experimenting showed that this would be possible with minimal 
tweaking and it would surely be beneficial in the long run.

- The compiled function body is stored in the probin field of pg_proc. 
This matches the historical split similar to adsrc/adbin, consrc/conbin, 
but this has now been abandoned.  Also, this field should ideally be of 
type pg_node_tree, so reusing probin for that is probably not good. 
Seems like a new field might be best.

- More test coverage is needed.  Surprisingly, there wasn't actually any 
test AFAICT that just creates and SQL function and runs it.  Most of 
that code is tested incidentally, but there is very little or no 
targeted testing of this functionality.

- Some of the changes in pg_proc.c, functioncmds.c, and functions.c in 
particular were jammed in and could use some reorganization after the 
basic ideas are solidified.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services