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
