/CREATE OR REPLACE FUNCTION get_status/
/RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS/
/$BODY$/
//
#variable_conflict use_variable
/DECLARE/
/change this to "s_status TEXT;"/ I think it's conflicting with the
column alias of same
//
/v_status TEXT;/
/BEGIN/
/ RETURN QUERY SELECT category, v_status as status, count (tool_id)
AS tool_count/
/ FROM /
/ (SELECT distinct category, tool_id, 'active' as v_status/
/FROM tools/
/ WHERE time >= 123456/
/ UNION/
/ SELECT distinct e1.category, e1.tool_id, 'inactive'
as v_status/
/ FROM tools e1/
/ WHERE not exists/
/ (SELECT e2.category, e2.tool_id/
/ FROM tools e2/
/ WHERE e2.sim_time >= 123456/
/ AND e2.category = e1.category/
/ AND e2.tool_id = e1.tool_id)/
/ ) AS derivedTable/
/GROUP BY category, Status;/
//
/END; $BODY$/
/LANGUAGE plpgsql;/
Thanks in advance for your insight or suggestion!
Michelle
