transforms - Mailing list pgsql-hackers

Here is my first patch for the transforms feature.  This is a mechanism
to adapt data types to procedural languages.  The previous proposal was
here: http://archives.postgresql.org/pgsql-hackers/2012-05/msg00728.php

At the surface, this contains:

- New catalog pg_transform

- CREATE/DROP TRANSFORM

As proof of concepts and hopefully useful applications to-be, I have
included transforms for

- PL/Perl - hstore
- PL/Python - hstore
- PL/Python - ltree

These, along with the documentation for the above-mentioned DDL commands
and catalog can serve as explanations for reviewers.

There are numerous remaining discussion points and possible work items:

*) Currently, each of the above provided transforms is put into a
separate subdirectory under contrib.  This might be OK, but it is mainly
because we cannot build more than one MODULE_big per directory.  Fixing
that might be more work than it's worth, but if we want to provide more
of these in contrib, it might get crowded.

*) Since we have separate extensions for plperl and plperlu, and also
for plpython2u and plpython3u, we need one extension for adapting each
of these to a given type.  You can see under contrib/hstore_plperl what
this looks like.  Maybe this isn't fixable or worth fixing.

(With the directory and packaging not finalized, I haven't included any
documentation for these contrib modules yet.)

*) No psql backslash commands yet.  Could be added.

*) Permissions: Transforms don't have owners, a bit like casts.
Currently, you are allowed to drop a transform if you own both the type
and the language.  That might be too strict, maybe own the type and have
privileges on the language would be enough.

*) If we want to offer the ability to write transforms to end users,
then we need to install all the header files for the languages and the
types.  This actually worked quite well; including hstore.h and plperl.h
for example, gave you what you needed.  In other cases, some headers
might need cleaning up.  Also, some magic from the plperl and plpython
build systems might need to be exposed, for example to find the header
files.  See existing modules for how this currently looks.

*) There is currently some syntax schizophrenia.  The grammar accepts

CREATE TRANSFORM FOR hstore LANGUAGE plperl (
    FROM SQL WITH hstore_to_plperl,
    TO SQL WITH plperl_to_hstore
);

but pg_dump produces

CREATE TRANSFORM FOR hstore LANGUAGE plperl (
    FROM SQL WITH hstore_to_plperl(hstore),
    TO SQL WITH plperl_to_hstore(internal)
);

The SQL standard allows both.  (In the same way that it allows 'DROP
FUNCTION foo' without arguments, if it is not ambigious.)  Precedent is
that CREATE CAST requires arguments, but CREATE LANGUAGE does not.

*) The issue of how to handle arguments of type "internal" was already
discussed at
http://archives.postgresql.org/pgsql-hackers/2012-05/msg00857.php and
following.  I have adopted the suggestion there to prohibit calling
functions with arguments of type "internal", but that is probably full
of holes and needs to be reviewed carefully.

*) I'm not very familiar with the Perl C API, so the hstore_plperl
implementation is probably full of memory leaks and weirdnesses.  It
needs some help from a PL/Perl expert.  (The PL/Python stuff is
hopefully better.)

*) ltree_plpython lacks the transform function from python to ltree,
because of laziness and lack of clear documentation on how to construct
ltree values.

*) I just noticed that the grammar changes broke ECPG.  I'll look into
that.

*) The regression tests for the core CREATE/DROP TRANSFORM functionality
is in contrib/hstore_plpython, because the core has no suitable language
available.  It's a bit ugly, but I don't know of a better way short of
implementing a fake language for regression testing only.