Can I JOIN using a custom type field?

Question

Have a type

CREATE TYPE map.get_near_link AS
   (link_id integer,
    distance integer,
    sentido integer,
    geom public.geometry(4));
ALTER TYPE map.get_near_link
  OWNER TO postgres;

My table has a field near_link and this work:

SELECT (near_link).link_id
FROM avl_pool

But if I want JOIN with my map table to get aditional info I cant do this

SELECT (near_link).link_id
FROM avl_pool a
JOIN map m
  ON a.near_link.link_id = m.link_id

I got error for near_link.

ERROR: missing FROM-clause entry for table "near_link" SQL state: 42P01 Character: 81

With

ON a.(near_link).link_id = v.link_id
               ^

ERROR: syntax error at or near "(" SQL state: 42601 Character: 83

Is there a way to make this join or should I get a subquery to get the link_id first, like this?

WITH getLink as (
   SELECT (near_link).link_id, * 
   from avl_pool a 
)
SELECT * 
FROM getLink g
JOIN map m
  ON g.link_id = m.link_id

Answer 1

You need to put the table alias inside the parentheses in your join condition:

SELECT (a.near_link).link_id
FROM avl_pool a
JOIN map m
  ON (a.near_link).link_id = m.link_id;

The parentheses are necessary to prevent the parser from interpreting the field name as a table name.

See section "8.16.3. Accessing Composite Types" of the documentation: https://www.postgresql.org/docs/9.3/static/rowtypes.html