Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group