transformations between types and languages

Here is a draft design for the transforms feature, which I'd like to
work on. The purpose of this is to allow adapting types to languages.
The most popular case is to enable converting hstore to something useful
like a dict or a hash in PL/Python or PL/Perl, respectively. In
general, the type and the language don't know of each other, and neither
need to be in core. Maybe you want to adapt PostGIS types to pygeometry
objects in PL/Python (made up example, but you get the idea).

What we basically need is a system catalog like this:

type -- the type to which this applies, e.g. hstore
lang -- e.g. plperl
fromsql -- function to convert from SQL to language-specific
tosql -- function to convert from language-specific to SQL

fromsql takes one argument of the respective type and returns internal.
tosql is the other way around. It's the responsibility of the language
handler to look up this information and use it. The "internal" argument
or return value will be something specific to the language
implementation and will likely be under the memory management of the
language handler.

The reason I call this transforms is that there is an SQL feature called
transforms. This was originally intended to allow adapting user-defined
types to client side languages, so it's about the same concept. If
there are concerns about overloading a standard feature like that, we
can change the name, but I fear there aren't going to be that many handy
synonyms available in the transform/translate/convert space.

Syntax examples:

CREATE LANGUAGE plpythonu ...;

CREATE TYPE hstore ...;

CREATE FUNCTION hstore_to_plpython(hstore) RETURNS internal ...;
CREATE FUNCTION plpython_to_hstore(internal) RETURNS hstore ...;

The actual implementation of these will look like the existing
PLyObject_ToBytea() and all those, except that instead of a hard-coded
switch statement, they will be selected through a system catalog.

CREATE TRANSFORM FOR hstore LANGUAGE plpythonu (
FROM SQL WITH hstore_to_plpython,
TO SQL WITH plpython_to_hstore);

If you have a plfoo/plfoou pair, you need to issue two statements like
that. But maybe we could offer the syntax LANGUAGE plperl, plperlu.

In practice, you would wrap this up in an extension which would depend
on hstore and plpython.

Peter Eisentraut wrote:
> Here is a draft design for the transforms feature, which I'd like to
> work on. The purpose of this is to allow adapting types to languages.
> The most popular case is to enable converting hstore to something useful
> like a dict or a hash in PL/Python or PL/Perl, respectively. In
> general, the type and the language don't know of each other, and neither
> need to be in core. Maybe you want to adapt PostGIS types to pygeometry
> objects in PL/Python (made up example, but you get the idea).

This is a good idea in principle.

I expect we should be able to use the same syntax both for system-defined types
and user-defined types.

I would expect, though, that in some common cases one can't avoid say having to
call hstore_to_plpython() directly, in order to disambiguate, and we may want to
provide terser syntax for using the desired TRANSFORM.

For example, if we have a Perl 5 hash, that could reasonably either map to an
hstore or to a tuple. Or a Perl 5 string with false utf8 flag could map to
either a character string or a byte string. Or a Perl 5 empty string (result of
1==0) could map to the false Boolean. Or a Perl 5 string that looks like a
number could map to either a character string or some kind of numeric. Or a
Perl 5 number 1 could map to either a numeric 1 (result of 1==1) or the true
Boolean.

Or we have to tighten the conversion rules so that things which are sometimes
equivalent and sometimes not on one side have different interpretations in the
transform.

Ideally the feature would also work not only for interfacing with PLs but also
with client languages, since conceptually its alike but just differing on who
calls who.

-- Darren Duncan

On Tue, May 15, 2012 at 4:15 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> [ draft design for the transforms feature ]

Seems pretty reasonable, although I'm not sure about your chosen
syntax for CREATE TRANSFORM...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Here is a draft design for the transforms feature, which I'd like to
> work on. ...

> fromsql takes one argument of the respective type and returns internal.
> tosql is the other way around. It's the responsibility of the language
> handler to look up this information and use it. The "internal" argument
> or return value will be something specific to the language
> implementation and will likely be under the memory management of the
> language handler.

This part is absolutely not gonna do, because it breaks the security
requirement that it not be possible to generate exposed "internal"
values from the SQL level. For instance there would be no way for the
type system to forbid

plperl_to_hstore(xml_to_plpython(some_xml_value))

despite the fact that this would almost assuredly crash. The slightly
more enterprising hacker might try using these functions to feed things
like btinsert(), leading to even more merriment.

Can we use something else for the magic type here? Or find a way to
positively forbid such functions from being called from the SQL level?

regards, tom lane

I wrote:
> Can we use something else for the magic type here? Or find a way to
> positively forbid such functions from being called from the SQL level?

When I wrote that I was wondering if we'd need a new pg_proc column,
"prodontcallfromsql" or some such. But on further reflection it seems
like it might be sufficient to just hack the parser to forbid any
user-level attempt to call a function that returns internal. The other
side of the coin, functions that take but don't return internal, would
be protected by means of the fact that there'd be no way to construct
matching argument values.

regards, tom lane