CODEWITHSUNDEEP
postgresqlstored-procedures
anagarD
anagarD

Reputation: 151

Stored procedure return type?

Is it possible for a stored procedure to return two different types?

CREATE OR REPLACE FUNCTION test_return_type(p_decide integer)
  RETURNS t_my_type AS
$BODY$DECLARE
  v_my_type t_my_type;
BEGIN
  IF p_decide = 1 THEN
    RETURN p_decide;
  ELSE
    --some code which will fill columns in v_my_type;
    RETURN v_my_type;
  END IF;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Definition of 't_my_type':

CREATE TYPE t_my_type AS
   (id integer,
    owner integer,
    type integer,
    time bigint,
    text character varying);

Code which uses procedure:

   SELECT * FROM test_return_type(1);

   SELECT * FROM test_return_type(2);

If this is not possible, should I fill v_my_type with some bogus data (when 1 is passed) just so I can return t_my_type as declared in procedure return type? Is there any better way than this?

I am using PostgreSQL v9.3 and pgAdmin III v1.18.0.

Upvotes: 1

Views: 1194

Answers (1)

Patrick
Patrick

Reputation: 32199

You can define your function with OUT parameters. In your example above the p_decide value would flag whether or not the v_my_type parameter has a meaningful value or not.

CREATE OR REPLACE FUNCTION test_return_type(OUT p_decide integer, OUT v_my_type t_my_type) RETURNS record AS $$
...

See the documentation on other options for returning data from functions.

Upvotes: 1

Related Questions