Обсуждение: Usage of RETURN QUERY
Hello, I'm trying to use RETURN QUERY in a function (see query below) in order to return list of issues and number of times when each issue occurred. When I try to run the query, it outputs: ERROR: syntax error at or near "RETURN" LINE 5: RETURN QUERY SELECT COUNT(tbl_rpc_data.data) AS count, tbl_r... ^ ********** Error ********** ERROR: syntax error at or near "RETURN" SQL state: 42601 I also tried running RETURN QUERY EXECUTE '...' USING ... -- the same result. I'm using 'PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit ' on 64-bit Windows 7 Ultimate. The text of the query: --DROP FUNCTION dbglogs_golden.get_grouped_issues_by_agents(integer[]); CREATE OR REPLACE FUNCTION dbglogs_golden.get_grouped_issues_by_agents(_p_agent_ids integer[]) RETURNS TABLE(_count integer, _desc text) AS $$ RETURN QUERY SELECT COUNT(tbl_rpc_data.data) AS count, tbl_rpc_data.data FROM dbglogs_golden.tbl_rpc_messages, dbglogs_debug.tbl_severity_levels, dbglogs_golden.tbl_rpc_data, dbglogs_product_agent.tbl_product_agents WHERE tbl_severity_levels.severity_level_id = tbl_rpc_messages.severity_level_id AND tbl_rpc_data.data_id = tbl_rpc_messages.data_id AND tbl_product_agents.product_agent_id = tbl_rpc_messages.product_agent_id AND tbl_rpc_messages.product_agent_id = ANY(_p_agent_ids) AND tbl_severity_levels.severity_level_id IN (1, 2, 4, 5) GROUP BY tbl_rpc_data.data ORDER BY count DESC; END; $$ LANGUAGE 'plpgsql'; ALTER FUNCTION dbglogs_golden.get_grouped_issues_by_agents(integer[]) OWNER TO ed;
"Eugene Dorofeyev" <edorofeyev@gmail.com> writes: > I'm trying to use RETURN QUERY in a function (see query below) in order to > return list of issues and number of times when each issue occurred. It looks like your problem is you forgot a BEGIN. > CREATE OR REPLACE FUNCTION > dbglogs_golden.get_grouped_issues_by_agents(_p_agent_ids integer[]) > RETURNS TABLE(_count integer, _desc text) AS $$ > RETURN QUERY SELECT COUNT(tbl_rpc_data.data) AS count, tbl_rpc_data.data > FROM dbglogs_golden.tbl_rpc_messages, dbglogs_debug.tbl_severity_levels, > dbglogs_golden.tbl_rpc_data, dbglogs_product_agent.tbl_product_agents > WHERE tbl_severity_levels.severity_level_id = > tbl_rpc_messages.severity_level_id AND > tbl_rpc_data.data_id = tbl_rpc_messages.data_id AND > tbl_product_agents.product_agent_id = > tbl_rpc_messages.product_agent_id AND > tbl_rpc_messages.product_agent_id = ANY(_p_agent_ids) AND > tbl_severity_levels.severity_level_id IN (1, 2, 4, 5) > GROUP BY tbl_rpc_data.data > ORDER BY count DESC; > END; > $$ LANGUAGE 'plpgsql'; regards, tom lane
Damn, I really need to buy a new glasses. Thank you for your help. But it returns data as a set of rows like (10354,"EXCEPTION: ...") (4363,"Access Violation Exception.") ... Is there a way to get columns as in a view ? -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, December 08, 2010 4:53 PM To: edorofeyev@gmail.com Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Usage of RETURN QUERY "Eugene Dorofeyev" <edorofeyev@gmail.com> writes: > I'm trying to use RETURN QUERY in a function (see query below) in > order to return list of issues and number of times when each issue occurred. It looks like your problem is you forgot a BEGIN.
"Eugene Dorofeyev" <edorofeyev@gmail.com> writes: > But it returns data as a set of rows like > (10354,"EXCEPTION: ...") > (4363,"Access Violation Exception.") > Is there a way to get columns as in a view ? Try "SELECT * FROM func(...)" rather than just "SELECT func(...)". regards, tom lane
Yes, 'SELECT * FROM' works fine -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, December 08, 2010 5:38 PM To: edorofeyev@gmail.com Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Usage of RETURN QUERY "Eugene Dorofeyev" <edorofeyev@gmail.com> writes: > But it returns data as a set of rows like > (10354,"EXCEPTION: ...") > (4363,"Access Violation Exception.") > Is there a way to get columns as in a view ? Try "SELECT * FROM func(...)" rather than just "SELECT func(...)". regards, tom lane