Skip site navigation (1) Skip section navigation (2)

Re: Usage of RETURN QUERY

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: edorofeyev(at)gmail(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Usage of RETURN QUERY
Date: 2010-12-08 14:52:37
Message-ID: 14558.1291819957@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Eugene Dorofeyev" <edorofeyev(at)gmail(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-12-08 15:13:43
Subject: Re: PGstatspack installation problem
Previous:From: Machiel RichardsDate: 2010-12-08 13:07:22
Subject: PGstatspack installation problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group