Thread: 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 tolanguage-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) RETURNShstore ...; 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@gmx.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@gmx.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