0037318: function which returns table

Description

We are creating a function which returns table and able to export and package it in functional state. But when we install this module, we get below error for the procedure and procedure does not get created.

[java] 55489 WARN - SQL Command failed with: ERROR: cannot use RETURN QUERY in a non-SETOF function
[java] Position: 1596
[java] 55497 WARN - -- END
[java] -- -----------------------------------------------------------------------
[java] -- FUNCTION API_GETDATA
[java] -- -----------------------------------------------------------------
[java] CREATE OR REPLACE FUNCTION API_GETDATA(pcs OUT NUMERIC, quantity OUT NUMERIC)
[java]

Is there any way or alternative for such procedures where we need to return a table from procedures

Steps To Reproduce

1. Create the below function

CREATE OR REPLACE FUNCTION api_getdata()
  RETURNS TABLE(pcs integer, quantity integer) AS
$BODY$
/******************************************************************************/
BEGIN
  RETURN QUERY select count(*) as pcs,round(qtyonhand,2) as quantity
from api_data;
END ; $BODY$
  LANGUAGE plpgsql VOLATILE

2. package it and install in other environment

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

causes

feature request

0052297

new

Triage Platform Base

Add SETOF return value

Notes
There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2017-11-17 10:30	jainsapna581	New Issue
2017-11-17 10:30	jainsapna581	Assigned To	=> Triage Finance
2017-11-17 10:30	jainsapna581	Web browser	=> Google Chrome
2017-11-17 10:30	jainsapna581	Modules	=> My Openbravo Tab
2017-11-17 10:30	jainsapna581	Regression level	=> Packaging and release
2017-11-17 10:30	jainsapna581	Triggers an Emergency Pack	=> No
2023-04-29 01:33	axelmercado	Relationship added	causes 0052297
2023-05-02 09:24	aferraz	Assigned To	Triage Omni OMS => Triage Platform Base