How to return a set of composite type without cast in PL/pgSQL?

Question

This works, but I need the casts (::name_value_pair) That seems a bit ugly to me. Is there a way to do it without the casts?

create type name_value_pair as (name text, value text);

create or replace function test1()
   returns setof name_value_pair
as $$
begin
    return next ROW('email', 'foo@example.com')::name_value_pair;
    return next ROW('user_id', 'abc123')::name_value_pair;
    return;
end;
$$ language plpgsql;

Here's the error without the cast. How does it not know it's a text? I thought a single quoted string literal was a text unless you cast it to something else.

psql: ERROR:  returned record type does not match expected record type
DETAIL:  Returned type unknown does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function test1() line 4 at RETURN NEXT

The select:

=> select * from test1();
  name   |      value      
---------+-----------------
 email   | foo@example.com
 user_id | abc123

I'm using PostgreSQL 12 beta.

Answer 1

The problem in your function is that the ROW constructor "strikes first", creating a composite type of two unknown values, for which there is no implicit type conversion.

Here are 5 ways without using an explicit cast.
Call each with:

SELECT * FROM ...;

1.

Educational mostly. Still using your predefined composite / row type, with explicit, verbose syntax. The subselect makes the unknown types default to text before they are composed into a ROW type:

CREATE OR REPLACE FUNCTION test1()
  RETURNS SETOF name_value_pair
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN NEXT (SELECT t FROM (SELECT 'email', 'foo@example.com') t);
   RETURN NEXT (SELECT t FROM (SELECT 'user_id', 'abc123') t);
END
$func$;

2.

Using RETURNS TABLE and RETURN QUERY instead. No need to form composite types to begin with, rather let the function itself do that final step:

CREATE OR REPLACE FUNCTION test2()
  RETURNS TABLE (name text, value text)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$;

3.

Or still use the composite type to define the return type. May be a better fit for you:

CREATE OR REPLACE FUNCTION test3()
  RETURNS SETOF name_value_pair
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$;

4.

While it's that simple, a single VALUES expression in a plain SQL function is shorter and faster:

CREATE OR REPLACE FUNCTION test4()
  RETURNS SETOF name_value_pair
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
VALUES
  ('email', 'foo@example.com')
, ('user_id', 'abc123')
;
$func$;

5.

If it has to be PL/pgSQL:

CREATE OR REPLACE FUNCTION test5()
  RETURNS SETOF name_value_pair
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY VALUES
     ('email', 'foo@example.com')
   , ('user_id', 'abc123')
   ;
END
$func$;

fiddle

The implicit conversion from unknown to text for scalar return values of a function was added in Postgres 10. Postgres 9.6 or older are stricter and would raise a similar error for alternatives 1 - 3. (Only function 4. & 5. work in any Postgres version.)

ERROR: structure of query does not match function result type
DETAIL: Returned type unknown does not match expected type text in column 1.

One might debate whether the still strict behavior for the ROW type was an oversight in that update or by design. A case could be made for either I guess.

PARALLEL SAFE for Postgres 9.6 or later.

Related:

• How to return result of a SELECT inside a function in PostgreSQL?

Answer 2

The problem here is that the string constants like 'email' are of type unknown not of type text.

You could rewrite your example like this:

create type name_value_pair as (name text, value text);

create or replace function test1()
   returns setof name_value_pair
as $$
begin
    return next ROW('email'::text, 'foo@example.com'::text);
    return next ROW('user_id'::text, 'abc123'::text);
    return;
end;
$$ language plpgsql;