There is a table ( main_table ) with the following fields:

id , custom_type[]

custom_type was created like this:

CREATE TYPE custom_type AS (param1 integer, param2 integer);

There are two more tables, where param1 is the foreign key of the foreign_table_1 table, and param2 the foreign_table_2 table.

How to make it so that you can JOIN these two tables to the custom_type array? The resulting query should look something like this:

 SELECT * FROM (SELECT custom_type FROM main_table WHERE id = 1) AS t LEFT JOIN foreign_table_1 ON t.param1 = foreign_table_1.id LEFT JOIN foreign_table_2 ON t.param2 = foreign_table_2.id

My problem is that I can not translate an array into a table. That is, making a request like this:

 SELECT t.param1 FROM (SELECT custom_type FROM main_table WHERE id = 1) AS t

I get the error that the t.param1 column does not exist.

Maybe there is some other solution without using the composite type. The main thing for me in the main_table to store an array of keys param1 , param2 .

    2 answers 2

    You can retrieve data from composite types by using parentheses. Your request should look like this:

     SELECT * FROM main_table AS t LEFT JOIN foreign_table_1 ON (t.custom_type).param1 = foreign_table_1.id LEFT JOIN foreign_table_2 ON (t.custom_type).param2 = foreign_table_2.id WHERE t.id = 1

    In your decision, I see at least two controversial points:

    1. Integrity constraints do not affect parts of composite types; therefore, you can insert any values ​​into the foreign_table_1 and foreig_table_2 tables , and postgres will not automatically check for the presence of these keys in the composite type of the main_table table.
    2. Parts of the composite type must be indexed separately. If you want, for example, param1 or param2 to be searched using an index, you have to use the so-called. “Computed indices”, and hang them on param1 and param2 for the custom_type field in the main_table table.

      The issue was resolved like this:

       WITH Data AS ( SELECT (unnest(custom_type)).* FROM main_table WHERE id=1 ) SELECT * FROM Data LEFT JOIN ...