databasepostgresqlunioncomposite-types

Why recursive union does not work with composite types in PostgreSQL


I have a table with fields of composite type. When I've tried to perform recursive union with such fields I got an error.

drop type example_t cascade;
create type example_t as (
    value text,
    key text
);

drop table if exists example cascade;
create table example (
    inbound example_t,
    outbound example_t,

    primary key (inbound, outbound)
);

create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
    with recursive target as (
        select outbound
            from example
            where array[inbound] <@ _attrs
        union
        select r.outbound
            from target as t
            inner join example as r on r.inbound = t.outbound
    )
    select unnest(_attrs)
    union
    select * from target;
$$ language sql immutable;


select example_fn(array[('foo', 'bar') ::example_t]);
ERROR: could not implement recursive UNION DETAIL: All column datatypes must be hashable. CONTEXT: SQL function "example_fn" during startup SQL state: 0A000

Non-recursive union just works

create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
    select unnest(_attrs)
    union
    select * from example;
$$ language sql immutable;

select example_fn(array[('foo', 'bar') ::example_t]);

I can refactor my function this way to make it works. But it looks weird. I mean it is less readable. Is there any way to do it better?

create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
    with recursive target as (
        select (outbound).value, (outbound).key
            from example
            where array[inbound] <@ _attrs
        union
        select (r.outbound).value, (r.outbound).key
            from target as t
            inner join example as r on r.inbound = (t.value, t.key) ::example_t
    )
    select (unnest(_attrs)).*
    union
    select * from target;
$$ language sql immutable;

Solution

  • There is a thread on PostgreSQL hackers mailing list and the short explanation by Tom Lane:

    In general we consider that a datatype's notion of equality can be defined either by its default btree opclass (which supports sort-based query algorithms) or by its default hash opclass (which supports hash-based query algorithms).

    The plain UNION code supports either sorting or hashing, but we've not gotten around to supporting a sort-based approach to recursive UNION. I'm not convinced that it's worth doing ...

    As a workaround use union all:

    with recursive target as (
        select outbound
        from example
        where inbound = ('a', 'a')::example_t
        union all
        select r.outbound
        from target as t
        inner join example as r on r.inbound = t.outbound
    )
    select *
    -- or, if necessary
    -- select distinct *
    from target