0037318: function which returns table - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0037318

Openbravo ERP

Z. Others

public

2017-11-17 10:30

2024-05-28 10:45

Reporter

jainsapna581

Assigned To

Triage Platform Base

Priority

urgent

Severity

minor

Reproducibility

always

Status

new

Resolution

open

Platform

OS Version

Product Version

3.0PR17Q1.1

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Google Chrome

Modules

My Openbravo Tab

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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

Proposed Solution

Additional Information