Tipos de compuestos de Postgresql

8.16.1. Declaración de tipos compuestos

8.16.2. Construyendo valores compuestos

8.16.3. Acceder a tipos compuestos

8.16.4. Modificación de tipos compuestos

8.16.5. Usar tipos compuestos en consultas

8.16.6. Sintaxis de entrada y salida de tipo compuesto

A tipo compuesto representa la estructura de una fila o registro; Básicamente, es solo una lista de nombres de campo y sus tipos de datos. PostgreSQL permite usar tipos compuestos de muchas de las mismas formas en que se pueden usar tipos simples. Por ejemplo, se puede declarar que una columna de una tabla es de tipo compuesto.

8.16.1. Declaración de tipos compuestos

Aquí hay dos ejemplos simples de definición de tipos compuestos:

CREATETYPE complex AS(
    r       doubleprecision,
    i       doubleprecision);CREATETYPE inventory_item AS(
    name            text,
    supplier_id     integer,
    price           numeric);

La sintaxis es comparable a CREATE TABLE, excepto que solo se pueden especificar nombres y tipos de campo; sin restricciones (como NOT NULL) actualmente puede incluirse. Tenga en cuenta que el AS la palabra clave es esencial; sin él, el sistema pensará en un tipo diferente de CREATE TYPE Se entiende el comando, y obtendrá extraños errores de sintaxis.

Una vez definidos los tipos, podemos utilizarlos para crear tablas:

CREATETABLE on_hand (
    item      inventory_item,
    count     integer);INSERTINTO on_hand VALUES(ROW('fuzzy dice',42,1.99),1000);

o funciones:

CREATEFUNCTION price_extension(inventory_item,integer)RETURNSnumericAS'SELECT $1.price * $2'LANGUAGESQL;SELECT price_extension(item,10)FROM on_hand;

Siempre que crea una tabla, también se crea automáticamente un tipo compuesto, con el mismo nombre que la tabla, para representar el tipo de fila de la tabla. Por ejemplo, habíamos dicho:

CREATETABLE inventory_item (
    name            text,
    supplier_id     integerREFERENCES suppliers,
    price           numericCHECK(price >0));

entonces lo mismo inventory_item El tipo compuesto que se muestra arriba vendría a existir como un subproducto, y podría usarse como arriba. Sin embargo, tenga en cuenta una restricción importante de la implementación actual: dado que no hay restricciones asociadas con un tipo compuesto, las restricciones que se muestran en la definición de la tabla no apliques a valores del tipo compuesto fuera de la tabla. (Para solucionar esto, cree un dominio sobre el tipo compuesto y aplique las restricciones deseadas como CHECK restricciones del dominio.)

8.16.2. Construyendo valores compuestos

Para escribir un valor compuesto como una constante literal, encierre los valores del campo entre paréntesis y sepárelos con comas. Puede poner comillas dobles alrededor de cualquier valor de campo y debe hacerlo si contiene comas o paréntesis. (A continuación se muestran más detalles). Por lo tanto, el formato general de una constante compuesta es el siguiente:

'( val1 , val2 , ... )'

Un ejemplo es:

'("fuzzy dice",42,1.99)'

que sería un valor válido de la inventory_item tipo definido anteriormente. Para hacer que un campo sea NULO, no escriba ningún carácter en su posición en la lista. Por ejemplo, esta constante especifica un tercer campo NULL:

'("fuzzy dice",42,)'

Si desea una cadena vacía en lugar de NULL, escriba comillas dobles:

'("",42,)'

Aquí el primer campo es una cadena vacía no NULL, el tercero es NULL.

(Estas constantes son en realidad solo un caso especial de las constantes de tipo genérico discutidas en la Sección 4.1.2.7. La constante se trata inicialmente como una cadena y se pasa a la rutina de conversión de entrada de tipo compuesto. Podría ser necesaria una especificación de tipo explícita para decir qué escriba para convertir la constante a.)

los ROW La sintaxis de expresión también se puede utilizar para construir valores compuestos. En la mayoría de los casos, esto es considerablemente más simple de usar que la sintaxis literal de cadena, ya que no tiene que preocuparse por múltiples capas de citas. Ya usamos este método arriba:

ROW('fuzzy dice',42,1.99)ROW('',42,NULL)

La palabra clave FILA es en realidad opcional siempre que tenga más de un campo en la expresión, por lo que estos se pueden simplificar a:

('fuzzy dice',42,1.99)('',42,NULL)

los ROW La sintaxis de expresión se analiza con más detalle en la Sección 4.2.13.

8.16.3. Acceder a tipos compuestos

Para acceder a un campo de una columna compuesta, se escribe un punto y el nombre del campo, muy parecido a seleccionar un campo de un nombre de tabla. De hecho, es tan parecido a seleccionar de un nombre de tabla que a menudo tienes que usar paréntesis para evitar confundir al analizador. Por ejemplo, puede intentar seleccionar algunos subcampos de nuestra on_hand tabla de ejemplo con algo como:

SELECT item.name FROM on_hand WHERE item.price >9.99;

Esto no funcionará ya que el nombre item se toma como un nombre de tabla, no un nombre de columna de on_hand, según las reglas de sintaxis SQL. Debes escribirlo así:

SELECT(item).name FROM on_hand WHERE(item).price >9.99;

o si también necesita usar el nombre de la tabla (por ejemplo, en una consulta de múltiples tablas), así:

SELECT(on_hand.item).name FROM on_hand WHERE(on_hand.item).price >9.99;

Ahora el objeto entre paréntesis se interpreta correctamente como una referencia al item columna, y luego se puede seleccionar el subcampo.

Se aplican problemas sintácticos similares siempre que selecciona un campo de un valor compuesto. Por ejemplo, para seleccionar solo un campo del resultado de una función que devuelve un valor compuesto, necesitaría escribir algo como:

SELECT(my_func(...)).field FROM...

Sin los paréntesis adicionales, esto generará un error de sintaxis.

El nombre del campo especial * medio “todos los campos“, como se explica con más detalle en la Sección 8.16.5.

8.16.4. Modificación de tipos compuestos

A continuación, se muestran algunos ejemplos de la sintaxis adecuada para insertar y actualizar columnas compuestas. Primero, insertando o actualizando una columna completa:

INSERTINTO mytab (complex_col)VALUES((1.1,2.2));UPDATE mytab SET complex_col =ROW(1.1,2.2)WHERE...;

El primer ejemplo omite ROW, el segundo lo usa; podríamos haberlo hecho de cualquier manera.

Podemos actualizar un subcampo individual de una columna compuesta:

UPDATE mytab SET complex_col.r =(complex_col).r +1WHERE...;

Observe aquí que no necesitamos (y de hecho no podemos) poner paréntesis alrededor del nombre de la columna que aparece justo después SET, pero necesitamos paréntesis al hacer referencia a la misma columna en la expresión a la derecha del signo igual.

Y podemos especificar subcampos como objetivos para INSERT, también:

INSERTINTO mytab (complex_col.r, complex_col.i)VALUES(1.1,2.2);

Si no hubiéramos proporcionado valores para todos los subcampos de la columna, los subcampos restantes se habrían llenado con valores nulos.

8.16.5. Usar tipos compuestos en consultas

Hay varias reglas de sintaxis especiales y comportamientos asociados con los tipos compuestos en las consultas. Estas reglas proporcionan atajos útiles, pero pueden resultar confusas si no conoce la lógica detrás de ellas.

En PostgreSQL, una referencia a un nombre de tabla (o alias) en una consulta es efectivamente una referencia al valor compuesto de la fila actual de la tabla. Por ejemplo, si tuviéramos una mesa inventory_item como se muestra arriba, podríamos escribir:

SELECT c FROM inventory_item c;

Esta consulta produce una sola columna con valores compuestos, por lo que podríamos obtener un resultado como:

           c
------------------------("fuzzy dice",42,1.99)(1row)

Sin embargo, tenga en cuenta que los nombres simples se hacen coincidir con los nombres de las columnas antes de los nombres de las tablas, por lo que este ejemplo solo funciona porque no hay ninguna columna nombrada c en las tablas de la consulta.

La sintaxis ordinaria de nombre de columna calificado table_name.column_name puede entenderse como aplicar la selección de campo al valor compuesto de la fila actual de la tabla. (Por razones de eficiencia, en realidad no se implementa de esa manera).

Cuando escribimos

SELECT c.*FROM inventory_item c;

luego, de acuerdo con el estándar SQL, deberíamos expandir el contenido de la tabla en columnas separadas:

    name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |42|1.99(1row)

como si la consulta fuera

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

PostgreSQL aplicará este comportamiento de expansión a cualquier expresión de valores compuestos, aunque como se muestra arriba, debe escribir paréntesis alrededor del valor que .* se aplica cuando no es un nombre de tabla simple. Por ejemplo, si myfunc() es una función que devuelve un tipo compuesto con columnas a, b, y c, estas dos consultas tienen el mismo resultado:

SELECT(myfunc(x)).*FROM some_table;SELECT(myfunc(x)).a,(myfunc(x)).b,(myfunc(x)).c FROM some_table;

Propina

PostgreSQL maneja la expansión de columnas transformando la primera forma en la segunda. Entonces, en este ejemplo, myfunc() se invocaría tres veces por fila con cualquier sintaxis. Si es una función costosa, es posible que desee evitarla, lo que puede hacer con una consulta como:

SELECT m.*FROM some_table, LATERAL myfunc(x)AS m;

Colocando la función en un LATERALFROM El elemento evita que se invoque más de una vez por fila. m.* todavía se expande en m.a, m.b, m.c, pero ahora esas variables son solo referencias a la salida del FROM artículo. (Los LATERAL La palabra clave es opcional aquí, pero la mostramos para aclarar que la función está obteniendo x de some_table.)

los composite_value.* la sintaxis da como resultado una expansión de columna de este tipo cuando aparece en el nivel superior de un SELECT lista de salida, una RETURNING lista en INSERT/UPDATE/DELETE, a VALUES cláusula o un constructor de filas. En todos los demás contextos (incluso cuando se anida dentro de una de esas construcciones), adjuntar .* a un valor compuesto no cambia el valor, ya que significa “todas las columnas“ y así se vuelve a producir el mismo valor compuesto. Por ejemplo, si somefunc() acepta un argumento de valor compuesto, estas consultas son las mismas:

SELECT somefunc(c.*)FROM inventory_item c;SELECT somefunc(c)FROM inventory_item c;

En ambos casos, la fila actual de inventory_item se pasa a la función como un único argumento con valores compuestos. Aunque .* no hace nada en tales casos, usarlo es un buen estilo, ya que deja en claro que se pretende un valor compuesto. En particular, el analizador considerará c en c.* para hacer referencia a un nombre de tabla o alias, no a un nombre de columna, para que no haya ambigüedad; mientras que sin .*, no está claro si c significa un nombre de tabla o un nombre de columna y, de hecho, se preferirá la interpretación del nombre de columna si hay una columna llamada c.

Otro ejemplo que demuestra estos conceptos es que todas estas consultas significan lo mismo:

SELECT*FROM inventory_item c ORDERBY c;SELECT*FROM inventory_item c ORDERBY c.*;SELECT*FROM inventory_item c ORDERBYROW(c.*);

Todos estos ORDER BY las cláusulas especifican el valor compuesto de la fila, lo que da como resultado la clasificación de las filas de acuerdo con las reglas descritas en la Sección 9.24.6. Sin embargo, si inventory_item contenía una columna llamada c, el primer caso sería diferente de los demás, ya que significaría ordenar solo por esa columna. Dados los nombres de columna mostrados anteriormente, estas consultas también son equivalentes a las anteriores:

SELECT*FROM inventory_item c ORDERBYROW(c.name, c.supplier_id, c.price);SELECT*FROM inventory_item c ORDERBY(c.name, c.supplier_id, c.price);

(El último caso usa un constructor de filas con la palabra clave ROW omitido.)

Otro comportamiento sintáctico especial asociado con los valores compuestos es que podemos usar notación funcional para extraer un campo de un valor compuesto. La forma sencilla de explicar esto es que las notaciones field(table) y table.field son intercambiables. Por ejemplo, estas consultas son equivalentes:

SELECT c.name FROM inventory_item c WHERE c.price >1000;SELECT name(c)FROM inventory_item c WHERE price(c)>1000;

Además, si tenemos una función que acepta un solo argumento de tipo compuesto, podemos llamarlo con cualquier notación. Estas consultas son todas equivalentes:

SELECT somefunc(c)FROM inventory_item c;SELECT somefunc(c.*)FROM inventory_item c;SELECT c.somefunc FROM inventory_item c;

Esta equivalencia entre la notación funcional y la notación de campo hace posible utilizar funciones en tipos compuestos para implementar “campos calculados“. Una aplicación que utilice la última consulta anterior no necesitaría saber directamente que somefunc no es una columna real de la tabla.

Propina

Debido a esto comportamiento, no es prudente dar a una función que toma un único argumento de tipo compuesto el mismo nombre que cualquiera de los campos de ese tipo compuesto. Si hay ambigüedad, se elegirá la interpretación del nombre del campo si se usa la sintaxis del nombre del campo, mientras que la función se elegirá si se usa la sintaxis de la llamada a la función. Sin embargo, las versiones de PostgreSQL anteriores a la 11 siempre eligieron la interpretación del nombre de campo, a menos que la sintaxis de la llamada requiriera que fuera una llamada a función. Una forma de forzar la interpretación de la función en versiones anteriores es calificar por esquema el nombre de la función, es decir, escribir schema.func(compositevalue).

8.16.6. Sintaxis de entrada y salida de tipo compuesto

La representación de texto externo de un valor compuesto consta de elementos que se interpretan de acuerdo con las reglas de conversión de E / S para los tipos de campos individuales, además de la decoración que indica la estructura compuesta. La decoración consta de paréntesis (( y )) alrededor del valor completo, más comas (,) entre elementos adyacentes. El espacio en blanco fuera de los paréntesis se ignora, pero dentro de los paréntesis se considera parte del valor del campo y puede o no ser significativo según las reglas de conversión de entrada para el tipo de datos del campo. Por ejemplo, en:

'(  42)'

el espacio en blanco se ignorará si el tipo de campo es entero, pero no si es texto.

Como se mostró anteriormente, al escribir un valor compuesto, puede escribir comillas dobles alrededor de cualquier valor de campo individual. usted debe hágalo si el valor del campo confundiría al analizador de valores compuestos. En particular, los campos que contienen paréntesis, comas, comillas dobles o barras invertidas deben estar entre comillas dobles. Para poner una comilla doble o una barra invertida en un valor de campo compuesto entre comillas, preceda con una barra invertida. (Además, se toma un par de comillas dobles dentro de un valor de campo entre comillas dobles para representar un carácter de comillas dobles, de manera análoga a las reglas para comillas simples en cadenas literales de SQL). todos los caracteres de datos que de otro modo se tomarían como sintaxis compuesta.

Un valor de campo completamente vacío (sin caracteres entre comas o paréntesis) representa un NULL. Para escribir un valor que sea una cadena vacía en lugar de NULL, escriba "".

La rutina de salida compuesta colocará comillas dobles alrededor de los valores de campo si son cadenas vacías o contienen paréntesis, comas, comillas dobles, barras invertidas o espacios en blanco. (Hacerlo para los espacios en blanco no es esencial, pero ayuda a la legibilidad). Se duplicarán las comillas dobles y las barras invertidas incrustadas en los valores de los campos.

Nota

Recuerde que lo que escriba en un comando SQL se interpretará primero como un literal de cadena y luego como un compuesto. Esto duplica la cantidad de barras invertidas que necesita (suponiendo que se use la sintaxis de cadena de escape). Por ejemplo, para insertar un text campo que contiene una comilla doble y una barra invertida en un valor compuesto, debe escribir:

INSERT...VALUES('(""\")');

El procesador de cadena literal elimina un nivel de barras invertidas, de modo que lo que llega al analizador de valores compuestos se parece a (""\"). A su vez, la cuerda alimentada al text la rutina de entrada del tipo de datos se convierte en ". (Si estuviéramos trabajando con un tipo de datos cuya rutina de entrada también tratara las barras invertidas especialmente, bytea por ejemplo, es posible que necesitemos hasta ocho barras invertidas en el comando para obtener una barra invertida en el campo compuesto almacenado.) Se pueden usar comillas en dólares (consulte la Sección 4.1.2.4) para evitar la necesidad de duplicar las barras invertidas.

Propina

los ROW La sintaxis del constructor suele ser más fácil de trabajar que la sintaxis literal compuesta cuando se escriben valores compuestos en comandos SQL. En ROW, los valores de los campos individuales se escriben de la misma forma en que se escribirían cuando no son miembros de un compuesto.