Re: SQL works but same function is confused

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