In plpgsql, return set of composite type without cast?

user_327572 0
Viewed 1160

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.

plpgsql postgresql
2 Answers

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:

1. Still using your predefined composite / row type, with explicit, verbose syntax (educational mostly). 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 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$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test1();

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) AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test2();

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 AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test3();

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

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

SELECT * FROM test4();

5. Or if it has to be PL/pgSQL:

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

SELECT * FROM test5();

db<>fiddle here

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.

Related:

user_9398600

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;
user_3213880
Related