On 04/01/2014 05:10 PM, Bui, Michelle P wrote:
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).
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;/
I am not seeing where v_status is being used. Below it is an alias name in the query, which is where the conflict is coming in.
/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
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
