On 04/01/2014 06: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).
/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
Then again, do you use "v_status" as a variable? select into v_status....
