A
A
Artem2018-04-27 10:03:11
PostgreSQL
Artem, 2018-04-27 10:03:11

How to explicitly refer to composite type columns in Postgre?

There is a composite type:

CREATE TYPE t_well AS (
  id INTEGER,
  name VARCHAR(10),
  altitude NUMERIC(10,3),
  x NUMERIC(10,6),
  y NUMERIC(10,6)
);

When using this type in queries, you must strictly follow the order of the columns in the definition. If I try to pass a variable of this type to a function:
SELECT * FROM p_well_operations_new(1, ROW(1, 'bla', 42, 1.99, 3.45));

then you cannot change the order of the columns, otherwise the values ​​will be written incorrectly.
How to explicitly specify columns in a composite type? Is it even possible?
Something like this for example:
SELECT * FROM p_well_operations_new(1, ROW(id=1, name='bla',...));

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
krypt3r, 2018-04-27
@krypt3r

When selecting a field from a value of a composite type, similar syntactic incidents are also possible. For example, to select one field from the result of a function that returns a composite value, you would write something like this:
SELECT (my_func(...)).field FROM ...
Without the extra parentheses, this query would generate a syntax error.

Although you have a slightly different question... Read the docs.

A
Artem, 2018-05-03
@artymail

I didn't find anything useful in the documentation.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question