SQL works but same function is confused

["Bui, Michelle P" <michelle.p.bui@xxxxxxxxxx>]

Hi all,

 

I have this query that when executed as a SQL statement, it works perfect! The table tools contains many records in a time series, with attributes like category but without the field status. I assign the value of status ’active’ or ‘inactive’ depending on whether the tool record exists after a certain time (number of seconds).

 

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 derived_table

GROUP BY category, Status

 

However, when I write a function to return the same result, using this SQL statement, and I declare a local variable v_status TEXT; it errors out when executed.  The key complaint is:

ERROR:  column reference "v_status" is ambiguous...

DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

 

The function returns table (category, status, and tool_count) using RETURN QUERY in front of the query.

I used <<block>> before Declare section and try using block.v_status but this is not allowed (has syntax error). When I use #variable_conflict use_variable, there is no error anymore, but the resulted Status field is null. Seemed like Postgresql does not assign the v_status as we wish.

 

CREATE OR REPLACE FUNCTION get_status

RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS

$BODY$

 

#variable_conflict use_variable

DECLARE

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