/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
>