Thread: Returning multiple values from a function

Hi All,

I wrote the following type and function to calculate and return two
values based on a number of inputs.

When I execute the function like this
SELECT *
    FROM interface.func_cover_dimensions(120.0::FLOAT4, 40.0::FLOAT4,
0::INT2, FALSE, NULL::FLOAT4, FALSE);

I get the following message.
ERROR: RETURN must specify a record or row variable in function
returning tuple at or near "{"
SQL state: 42601
Context: compile of PL/pgSQL function "func_cover_dimensions" near line 49

I am reasonably sure that I have simply bollixed the concept of
returning more than one value from a function.  Can someone point out
the error of my ways?  Links to relevant documentation are always
appreciated.

-- Type: interface.func_cover_dimensions
-- DROP TYPE interface.func_cover_dimensions;

CREATE TYPE interface.func_cover_dimensions AS
    (cover_length_in float4,
     cover_width_in  float4);
ALTER TYPE interface.func_cover_dimensions OWNER TO postgres;


-- Function: interface.func_cover_dimensions(float4, float4, int2, bool,
float4, bool)

-- DROP FUNCTION interface.func_cover_dimensions(float4, float4, int2,
bool, float4, bool)

CREATE OR REPLACE FUNCTION interface.func_cover_dimensions(float4,
float4, int2, bool, float4, bool)
   RETURNS interface.func_cover_dimensions AS
$BODY$
    DECLARE
       v_length_in     ALIAS FOR $1;
       v_width_in      ALIAS FOR $2;
       v_box_quantity  ALIAS FOR $3;
       v_floating_box  ALIAS FOR $4;
       v_box_length_in ALIAS FOR $5;
       v_catwalk       ALIAS FOR $6;
       v_quantity      FLOAT4;
       v_bar_length_in FLOAT4;
       v_bar_width_in  FLOAT4;
       v_cover_length  FLOAT4;
       v_cover_width   FLOAT4;
    BEGIN
--    If width is greater than length reverse their values.
       IF v_width_in  >  v_length_in THEN
          v_bar_width_in  := v_length_in;
          v_bar_length_in := v_width_in;
       ELSE
          v_bar_width_in  := v_width_in;
          v_bar_length_in := v_length_in;
       END IF;
       IF v_floating_box THEN
          v_cover_length = v_bar_length_in - (0.5::FLOAT4 * v_box_length_in)
       ELSE
--       Calculate the length and width of the cover.
          v_cover_length = v_bar_length_in - (v_box_quantity::FLOAT4 *
v_box_length_in)
       END IF;
--    Calculate the width of the cover.
       v_cover_width = v_bar_width_in - 12::FLOAT4

       RETURN v_cover_length, v_cover_width};
    END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;
--
Kind Regards,
Keith

Keith Worthington <KeithW@NarrowPathInc.com> writes:
> I am reasonably sure that I have simply bollixed the concept of
> returning more than one value from a function.

I think you've bollixed putting a semicolon at the end of each
statement; also the right brace "}" at the end of the RETURN statement
doesn't match anything.  Fix the simple syntax errors first, then maybe
the more complicated problems will get more transparent ...

            regards, tom lane