Re: SQL works but same function is confused

Rob Sargent <robjsargent@xxxxxxxxx> · Tue, 01 Apr 2014 18:22:36 -0600



    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