vzZFGJmq

1-- Returns all slots associated with an object
2create or replace function object_slots(object_id varchar) returns table(id varchar,
3		ord int,
4		name varchar,
5		slot_type varchar,
6		value numeric,
7		element varchar,
8		mass numeric,
9		closure numeric) as $$
10	declare
11		inherited varchar[];
12   begin
13		inherited := array( select all_inherited(object_id) );
14		return query select * from slots s where s.id = any(inherited) order by ord;
15   end;
16$$ language plpgsql;
17
18-- Returns all plugs associated with an object
19create or replace function object_plugs(object_id varchar) returns table(id varchar,
20		ord int,
21		name varchar,
22		slot_type varchar,
23		value numeric,
24		element varchar,
25		mass numeric) as $$
26	declare
27		inherited varchar[];
28   begin
29		inherited := array( select all_inherited(object_id) );
30		return query select * from plugs p where p.id = any(inherited) order by ord;
31	end;
32$$ language plpgsql;
33
34/* Returns a table with the values needed to link a pair of objects as parent and child.
35	Mandatory fields in the input are the parent and child ids.
36	Optional fields are: (slot_type or slot_name) and (plug_type or plug_name).
37 */
38create or replace function prepare_parent_link(input jsonb) returns table (success boolean, slot_name varchar, plug_name varchar) as $$
39   declare
40		parent_slots record;
41		child_plugs record;
42		winner_slots record;
43		winner_plugs record;
44   begin
45		-- Input must have keys 'child' and 'parent'
46	 	if not exists input->>'child' or not exists input->>'parent'
47         perform log_event('info', concat( 'prepare_parent_link ', jsonb_pretty(input) ), 'No child or parent provchilded');
48         raise exception 'No child or parent provchilded';
49			return 1;
50		end if;
51		-- Fill the slots records using whatever is available
52		if exists input->'slot_name'
53			select object_slots(input->>'parent') where      name = input->'slot_name'   into parent_slots;
54		elseif exists input->'slot_type'
55			select object_slots(input->>'parent') where slot_type = input->'slot_type'   into parent_slots;
56		else
57			select object_plugs(input->>'parent')                                        into parent_slots;
58		end if;
59		-- Fill the plugs records
60		if exists input->'plug_name'
61			select object_plugs(input->>'child')  where      name = input->'plug_name'   into child_plugs;
62		elseif exists input->'plug_type'
63			select object_plugs(input->>'child')  where slot_type = input->'plug_type'   into child_plugs;
64		else
65			select object_plugs(input->>'child')                                         into child_plugs;
66		end if;
67		-- Get the parent slots that match some child plug
68		select * from parent_slots p where p.slot_type = any( array(select distinct slot_type from child_plugs) ) into winner_slots;
69   end;
70$$ language plpgsql;