znvrAZ3p

1drop type example_t cascade;
2create type example_t as (
3    value text,
4    key text
5);
6
7drop table if exists example cascade;
8create table example (
9    inbound example_t,
10    outbound example_t,
11
12    primary key (inbound, outbound)
13);
14
15create or replace function example_fn(_attrs example_t[])
16returns table (attr example_t) as $$
17    with recursive target as (
18        select outbound
19            from example
20            where array[inbound] <@ _attrs
21        union
22        select r.outbound
23            from target as t
24            inner join example as r on r.inbound = t.outbound
25    )
26    select unnest(_attrs)
27    union
28    select * from target;
29$$ language sql immutable;
30
31
32select example_fn(array[('foo', 'bar') ::example_t]);
33ERROR: could not implement recursive UNION DETAIL: All column datatypes must be hashable. CONTEXT: SQL function "example_fn" during startup SQL state: 0A000
34	
35create or replace function example_fn(_attrs example_t[])
36returns table (attr example_t) as $$
37    select unnest(_attrs)
38    union
39    select * from example;
40$$ language sql immutable;
41
42select example_fn(array[('foo', 'bar') ::example_t]);
43	
44create or replace function example_fn(_attrs example_t[])
45returns table (attr example_t) as $$
46    with recursive target as (
47        select (outbound).value, (outbound).key
48            from example
49            where array[inbound] <@ _attrs
50        union
51        select (r.outbound).value, (r.outbound).key
52            from target as t
53            inner join example as r on r.inbound = (t.value, t.key) ::example_t
54    )
55    select (unnest(_attrs)).*
56    union
57    select * from target;
58$$ language sql immutable;