CODEWITHSUNDEEP
sqldatabasepostgresql
Larry
Larry

Reputation: 11919

Is it possible to have PostgreSQL function return “arbitrary” type?

Is it possible to have an arbitrary return type defined for some PostgreSQL function?

The idea is that depending on the call, the output may be returned differently.

For example, let’s say we have:

TypeA : (name, email)
TypeB : (name, email, address, phone)

We may have a function:

func1(name varchar);

But return type could be either: TypeA or TypeB

So, is it possible to define func1, so that the arbitrary return type works?

EDIT:

IF the solution is refcursor ... Could someone please write an answer based on the example in my question? That would help a lot!

Upvotes: 0

Views: 364

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

You have a few options. The first is to use a polymorphic type which would be cast on call, the second would be to use a cast, and a third would be to return a refcursor.

Polymorphic type

In this case, you'd do something like:

 CREATE FUNCTION foo (bar varchar, baz ANYELEMENT) returns ANYELEMENT AS
 $$
   SELECT 'test'::TEXT;
 $$ language sql;

Then to call it you would cast the NULL argument on call:

SELECT * FROM foo('test', null::varchar);

The real problem you have with this is that you are going to have to specify a type on every call.

Single Return type with Cast

In your example, one type has a subset of fields of another type. So you could:

CREATE TYPE all_info AS (
   name text,
   email text,
   address text,
   phone text
);
CREATE TYPE email_only AS (
   name text,
   email text
);
CREATE FUNCTION email_only(all_info) returns email_only LANGUAGE SQL IMMUTABLE AS $$
SELECT $1.name, $1.email;
$$;
CREATE CAST (all_info as email_only) WITH FUNCTION email_only(all_info);

Then you create your function to return all_info and you can cast on output. Something like:

SELECT (f::email_only).* FROM my_function('foo') f;

Note these two allow you to use SQL language functions which refcursors do not.

Refcursor

In this case you have to use plpgsql

CREATE OR REPLACE FUNCTION foo(bar varchar) RETURNS refcursor LANGUAGE plpgsql AS
$$
DECLARE a REFCURSOR;
BEGIN
   OPEN a FOR SELECT ....;
   RETURN a;
END;
$$;

In general I think it is easiest to start with the superset and cast approach than it is the others. Refcursors are possibly a second approach. Last would be insisting on a cast for a type.

Upvotes: 4

Related Questions