Netezza - How to return multiple values from a stored procedure without REFTABLE

Question

I try to return a few values from a stored procedure without using reftable in Netezza.

CREATE OR REPLACE PROCEDURE TMP_GETTABLEINFO(CHAR(any), VARCHAR(any), VARCHAR(any)) returns char(1), varchar(30) EXECUTE AS OWNER LANGUAGE NZPLSQL AS begin_proc DECLARE a ALIAS FOR $1; b ALIAS FOR $2; BEGIN ... RETURN a, b; END;

Is it possible? How?

My results are: ERROR [HY000] ERROR: Must specify LANGUAGE

Answer 1

I don't think this is possible. I haven't seen a case with multiple values being returned.

From the documentation (Netezza_stored_procedures_guide.pdf): Typically, an NZPLSQL procedure returns a unique return value, but it can also return a result set in the form of a specified table.

To create a stored procedure that returns a result set:

• Define the stored procedure with a return value of RETURNS REFTABLE (<table-name>) to indicate that it returns a result set that looks like the specified table.
• Inside the body of the procedure, use the variable REFTABLENAME to refer to the results table.