RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS$BODY$ #variable_conflict use_variableDECLARE
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_statusFROM 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 derivedTableGROUP BY category, Status; END; $BODY$LANGUAGE plpgsql; Thanks in advance for your insight or suggestion! Michelle
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
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных