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;

Search Discussions

  • Tom Lane at Dec 8, 2010 at 2:52 pm

    "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
  • Eugene Dorofeyev at Dec 8, 2010 at 3:14 pm
    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
    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.
  • Tom Lane at Dec 8, 2010 at 3:38 pm

    "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
  • Eugene Dorofeyev at Dec 8, 2010 at 3:45 pm
    Yes, 'SELECT * FROM' works fine


    -----Original Message-----
    From: Tom Lane
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 8, '10 at 10:40a
activeDec 8, '10 at 3:45p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Eugene Dorofeyev: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase