Returning multiple values from a function

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Returning multiple values from a function
Date: 2007-09-11 03:54:06
Message-ID: 46E6115E.1050901@NarrowPathInc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jason Tan Boon Teck 2007-09-11 04:01:01 Re: pg_restore and pgadmin3
Previous Message Tom Lane 2007-09-11 00:31:22 Re: the copy command