From cac76582053ef8ea07df65fed0757f352da23705 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Sun, 26 Apr 2015 10:33:14 -0400
Subject: Add transforms feature
This provides a mechanism for specifying conversions between SQL data
types and procedural languages. As examples, there are transforms
for hstore and ltree for PL/Perl and PL/Python.
reviews by Pavel Stěhule and Andres Freund
---
doc/src/sgml/catalogs.sgml | 82 +++++++++++++
doc/src/sgml/hstore.sgml | 19 +++
doc/src/sgml/information_schema.sgml | 85 ++++++++++++++
doc/src/sgml/ltree.sgml | 15 +++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/alter_extension.sgml | 21 ++++
doc/src/sgml/ref/comment.sgml | 22 ++++
doc/src/sgml/ref/create_function.sgml | 18 +++
doc/src/sgml/ref/create_transform.sgml | 207 +++++++++++++++++++++++++++++++++
doc/src/sgml/ref/drop_transform.sgml | 123 ++++++++++++++++++++
doc/src/sgml/reference.sgml | 2 +
11 files changed, 596 insertions(+)
create mode 100644 doc/src/sgml/ref/create_transform.sgml
create mode 100644 doc/src/sgml/ref/drop_transform.sgml
(limited to 'doc/src')
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4e6fd0e06fb..898865eea19 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -273,6 +273,11 @@
tablespaces within this database cluster
+
+ pg_transform
+ transforms (data type to procedural language conversions)
+
+
pg_triggertriggers
@@ -5071,6 +5076,15 @@
+
+ protrftypes
+ oid[]
+
+
+ Data type OIDs for which to apply transforms.
+
+
+
prosrctext
@@ -6071,6 +6085,74 @@
+
+ <structname>pg_transform</structname>
+
+
+ pg_transform
+
+
+
+ The catalog pg_transform stores information about
+ transforms, which are a mechanism to adapt data types to procedural
+ languages. See for more information.
+
+
+
+ <structname>pg_transform</> Columns
+
+
+
+
+ Name
+ Type
+ References
+ Description
+
+
+
+
+
+ trftype
+ oid
+ pg_type.oid
+ OID of the data type this transform is for
+
+
+
+ trflang
+ oid
+ pg_language.oid
+ OID of the language this transform is for
+
+
+
+ trffromsql
+ regproc
+ pg_proc.oid
+
+ The OID of the function to use when converting the data type for input
+ to the procedural language (e.g., function parameters). Zero is stored
+ if this operation is not supported.
+
+
+
+
+ trftosql
+ regproc
+ pg_proc.oid
+
+ The OID of the function to use when converting output from the
+ procedural language (e.g., return values) to the data type. Zero is
+ stored if this operation is not supported.
+
+
+
+
+
+
+
+
<structname>pg_trigger</structname>
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index fbe9543dfea..94f01f8dfe7 100644
--- a/doc/src/sgml/hstore.sgml
+++ b/doc/src/sgml/hstore.sgml
@@ -596,6 +596,25 @@ ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
+
+ Transforms
+
+
+ Additional extensions are available that implement transforms for
+ the hstore type for the languages PL/Perl and PL/Python. The
+ extensions for PL/Perl are called hstore_plperl
+ and hstore_plperlu, for trusted and untrusted PL/Perl.
+ If you install these transforms and specify them when creating a
+ function, hstore values are mapped to Perl hashes. The
+ extensions for PL/Python are
+ called hstore_plpythonu, hstore_plpython2u,
+ and hstore_plpython3u
+ (see for the PL/Python naming
+ convention). If you use them, hstore values are mapped to
+ Python dictionaries.
+
+
+
Authors
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 22f43c8a5bf..ca1f20b3385 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5519,6 +5519,91 @@ ORDER BY c.ordinal_position;
+
+ <literal>transforms</literal>
+
+
+ The view transforms contains information about the
+ transforms defined in the current database. More precisely, it contains a
+ row for each function contained in a transform (the from SQL
+ or to SQL function).
+
+
+
+ <literal>transforms</literal> Columns
+
+
+
+
+ Name
+ Data Type
+ Description
+
+
+
+
+
+ udt_catalog
+ sql_identifier
+ Name of the database that contains the type the transform is for (always the current database)
+
+
+
+ udt_schema
+ sql_identifier
+ Name of the schema that contains the type the transform is for
+
+
+
+ udt_name
+ sql_identifier
+ Name of the type the transform is for
+
+
+
+ specific_catalog
+ sql_identifier
+ Name of the database containing the function (always the current database)
+
+
+
+ specific_schema
+ sql_identifier
+ Name of the schema containing the function
+
+
+
+ specific_name
+ sql_identifier
+
+ The specific name of the function. See for more information.
+
+
+
+
+ group_name
+ sql_identifier
+
+ The SQL standard allows defining transforms in groups,
+ and selecting a group at run time. PostgreSQL does not support this.
+ Instead, transforms are specific to a language. As a compromise, this
+ field contains the language the transform is for.
+
+
+
+
+ transform_type
+ character_data
+
+ FROM SQL or TO SQL
+
+
+
+
+
+
+
<literal>triggered_update_columns</literal>
diff --git a/doc/src/sgml/ltree.sgml b/doc/src/sgml/ltree.sgml
index cd8a061c943..3f87319bf96 100644
--- a/doc/src/sgml/ltree.sgml
+++ b/doc/src/sgml/ltree.sgml
@@ -664,6 +664,21 @@ ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.
+
+ Transforms
+
+
+ Additional extensions are available that implement transforms for
+ the ltree type for PL/Python. The extensions are
+ called ltree_plpythonu, ltree_plpython2u,
+ and ltree_plpython3u
+ (see for the PL/Python naming
+ convention). If you install these transforms and specify them when
+ creating a function, ltree values are mapped to Python lists.
+ (The reverse is currently not supported, however.)
+
+
+
Authors
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f09fc250f08..bf95453b6c6 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -79,6 +79,7 @@ Complete list of usable sgml source files in this directory.
+
@@ -120,6 +121,7 @@ Complete list of usable sgml source files in this directory.
+
diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml
index 0d479c8ca2d..7141ee352eb 100644
--- a/doc/src/sgml/ref/alter_extension.sgml
+++ b/doc/src/sgml/ref/alter_extension.sgml
@@ -52,6 +52,7 @@ ALTER EXTENSION name DROP object_name |
TEXT SEARCH PARSER object_name |
TEXT SEARCH TEMPLATE object_name |
+ TRANSFORM FOR type_name LANGUAGE lang_name |
TYPE object_name |
VIEW object_name
@@ -259,6 +260,26 @@ ALTER EXTENSION name DROP
+
+
+ type_name
+
+
+
+ The name of the data type of the transform.
+
+
+
+
+
+ lang_name
+
+
+
+ The name of the language of the transform.
+
+
+
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 62e1968c08d..656f5aae5ba 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -55,6 +55,7 @@ COMMENT ON
TEXT SEARCH DICTIONARY object_name |
TEXT SEARCH PARSER object_name |
TEXT SEARCH TEMPLATE object_name |
+ TRANSFORM FOR type_name LANGUAGE lang_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
@@ -225,6 +226,26 @@ COMMENT ON
+
+ type_name
+
+
+
+ The name of the data type of the transform.
+
+
+
+
+
+ lang_name
+
+
+
+ The name of the language of the transform.
+
+
+
+
text
@@ -305,6 +326,7 @@ COMMENT ON TEXT SEARCH CONFIGURATION my_config IS 'Special word filtering';
COMMENT ON TEXT SEARCH DICTIONARY swedish IS 'Snowball stemmer for Swedish language';
COMMENT ON TEXT SEARCH PARSER my_parser IS 'Splits text into words';
COMMENT ON TEXT SEARCH TEMPLATE snowball IS 'Snowball stemmer';
+COMMENT ON TRANSFORM FOR hstore LANGUAGE plpythonu IS 'Transform between hstore and Python dict';
COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for RI';
COMMENT ON TYPE complex IS 'Complex number data type';
COMMENT ON VIEW my_view IS 'View of departmental costs';
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 20019215351..c5beb166cfa 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -25,6 +25,7 @@ CREATE [ OR REPLACE ] FUNCTION
[ RETURNS rettype
| RETURNS TABLE ( column_namecolumn_type [, ...] ) ]
{ LANGUAGE lang_name
+ | TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
@@ -260,6 +261,23 @@ CREATE [ OR REPLACE ] FUNCTION
+
+ TRANSFORM { FOR TYPE type_name } [, ... ] }
+
+
+
+ Lists which transforms a call to the function should apply. Transforms
+ convert between SQL types and language-specific data types;
+ see . Procedural language
+ implementations usually have hardcoded knowledge of the built-in types,
+ so those don't need to be listed here. If a procedural language
+ implementation does not know how to handle a type and no transform is
+ supplied, it will fall back to a default behavior for converting data
+ types, but this depends on the implementation.
+
+
+
+
WINDOW
diff --git a/doc/src/sgml/ref/create_transform.sgml b/doc/src/sgml/ref/create_transform.sgml
new file mode 100644
index 00000000000..d321dad7a56
--- /dev/null
+++ b/doc/src/sgml/ref/create_transform.sgml
@@ -0,0 +1,207 @@
+
+
+
+
+ CREATE TRANSFORM
+
+
+
+ CREATE TRANSFORM
+ 7
+ SQL - Language Statements
+
+
+
+ CREATE TRANSFORM
+ define a new transform
+
+
+
+
+CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name (
+ FROM SQL WITH FUNCTION from_sql_function_name (argument_type [, ...]),
+ TO SQL WITH FUNCTION to_sql_function_name (argument_type [, ...])
+);
+
+
+
+
+ Description
+
+
+ CREATE TRANSFORM defines a new transform.
+ CREATE OR REPLACE TRANSFORM will either create a new
+ transform, or replace an existing definition.
+
+
+
+ A transform specifies how to adapt a data type to a procedural language.
+ For example, when writing a function in PL/Python using
+ the hstore type, PL/Python has no prior knowledge how to
+ present hstore values in the Python environment. Language
+ implementations usually default to using the text representation, but that
+ is inconvenient when, for example, an associative array or a list would be
+ more appropriate.
+
+
+
+ A transform specifies two functions:
+
+
+
+ A from SQL function that converts the type from the SQL
+ environment to the language. This function will be invoked on the
+ arguments of a function written in the language.
+
+
+
+
+
+ A to SQL function that converts the type from the
+ language to the SQL environment. This function will be invoked on the
+ return value of a function written in the language.
+
+
+
+ It is not necessary to provide both of these functions. If one is not
+ specified, the language-specific default behavior will be used if
+ necessary. (To prevent a transformation in a certain direction from
+ happening at all, you could also write a transform function that always
+ errors out.)
+
+
+
+ To be able to create a transform, you must own and
+ have USAGE privilege on the type, have
+ USAGE privilege on the language, and own and
+ have EXECUTE privilege on the from-SQL and to-SQL
+ functions, if specified.
+
+
+
+
+ Parameters
+
+
+
+ type_name
+
+
+
+ The name of the data type of the transform.
+
+
+
+
+
+ lang_name
+
+
+
+ The name of the language of the transform.
+
+
+
+
+
+ from_sql_function_name(argument_type [, ...])
+
+
+
+ The name of the function for converting the type from the SQL
+ environment to the language. It must take one argument of
+ type internal and return type internal. The
+ actual argument will be of the type for the transform, and the function
+ should be coded as if it were. (But it is not allowed to declare an
+ SQL-level function function returning internal without at
+ least one argument of type internal.) The actual return
+ value will be something specific to the language implementation.
+
+
+
+
+
+ to_sql_function_name(argument_type [, ...])
+
+
+
+ The name of the function for converting the type from the language to
+ the SQL environment. It must take one argument of type
+ internal and return the type that is the type for the
+ transform. The actual argument value will be something specific to the
+ language implementation.
+
+
+
+
+
+
+
+ Notes
+
+
+ Use to remove transforms.
+
+
+
+
+ Examples
+
+
+ To create a transform for type hstore and language
+ plpythonu, first set up the type and the language:
+
+CREATE TYPE hstore ...;
+
+CREATE LANGUAGE plpythonu ...;
+
+ Then create the necessary functions:
+
+CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS ...;
+
+CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
+LANGUAGE C STRICT IMMUTABLE
+AS ...;
+
+ And finally create the transform to connect them all together:
+
+CREATE TRANSFORM FOR hstore LANGUAGE plpythonu (
+ FROM SQL WITH FUNCTION hstore_to_plpython(internal),
+ TO SQL WITH FUNCTION plpython_to_hstore(internal)
+);
+
+ In practice, these commands would be wrapped up in extensions.
+
+
+
+ The contrib section contains a number of extensions
+ that provide transforms, which can serve as real-world examples.
+
+
+
+
+ Compatibility
+
+
+ This form of CREATE TRANSFORM is a
+ PostgreSQL extension. There is a CREATE
+ TRANSFORM command in the SQL standard, but it
+ is for adapting data types to client languages. That usage is not supported
+ by PostgreSQL.
+
+
+
+
+ See Also
+
+
+ ,
+ ,
+ ,
+
+
+
+
+
diff --git a/doc/src/sgml/ref/drop_transform.sgml b/doc/src/sgml/ref/drop_transform.sgml
new file mode 100644
index 00000000000..59ff87cfe48
--- /dev/null
+++ b/doc/src/sgml/ref/drop_transform.sgml
@@ -0,0 +1,123 @@
+
+
+
+
+ DROP TRANSFORM
+
+
+
+ DROP TRANSFORM
+ 7
+ SQL - Language Statements
+
+
+
+ DROP TRANSFORM
+ remove a transform
+
+
+
+
+DROP TRANSFORM [ IF EXISTS ] FOR type_name LANGUAGE lang_name
+
+
+
+
+ Description
+
+
+ DROP TRANSFORM removes a previously defined transform.
+
+
+
+ To be able to drop a transform, you must own the type and the language.
+ These are the same privileges that are required to create a transform.
+
+
+
+
+ Parameters
+
+
+
+
+ IF EXISTS
+
+
+ Do not throw an error if the transform does not exist. A notice is issued
+ in this case.
+
+
+
+
+
+ type_name
+
+
+
+ The name of the data type of the transform.
+
+
+
+
+
+ lang_name
+
+
+
+ The name of the language of the transform.
+
+
+
+
+
+ CASCADE
+
+
+ Automatically drop objects that depend on the transform.
+
+
+
+
+
+ RESTRICT
+
+
+ Refuse to drop the transform if any objects depend on it. This is the
+ default.
+
+
+
+
+
+
+
+ Examples
+
+
+ To drop the transform for type hstore and language
+ plpythonu:
+
+DROP TRANSFORM FOR hstore LANGUAGE plpythonu;
+
+
+
+
+ Compatibility
+
+
+ This form of DROP TRANSFORM is a
+ PostgreSQL extension. See for details.
+
+
+
+
+ See Also
+
+
+
+
+
+
+
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index c52eb28c8c4..03020dfec42 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -111,6 +111,7 @@
&createTSDictionary;
&createTSParser;
&createTSTemplate;
+ &createTransform;
&createTrigger;
&createType;
&createUser;
@@ -152,6 +153,7 @@
&dropTSDictionary;
&dropTSParser;
&dropTSTemplate;
+ &dropTransform;
&dropTrigger;
&dropType;
&dropUser;
--
cgit v1.2.3