Incorrect behavior of header Prefer: params=single-object on overloaded function

[fortyanov]

• PostgreSQL version: 12.1
• PostgREST version: 6.0.2 (713b214)
• Operating system: Ubuntu 19.04

Incorrect behavior of header Prefer: params=single-object

We have got overridden function

CREATE TYPE test_type AS
(
    "p1" text,
    "p2" text
);

CREATE OR REPLACE FUNCTION test(a text, b text) RETURNS test_type
    LANGUAGE PLpgSQL
AS
$_$
BEGIN
    RETURN ROW (a, b);
END
$_$;

CREATE OR REPLACE FUNCTION test(json) RETURNS json
    LANGUAGE PLpgSQL
AS
$_$
BEGIN
    RETURN $1;
END
$_$;

send request with header
Prefer: params=single-object

curl --location --request POST 'http://127.0.0.1:3001/rpc/test' \
--header 'Content-Type: application/json' \
--header 'Prefer: params=single-object' \
--data-raw '{
  "a": "a",
  "b": "b"
}'

returns incorrect response

[
  {
    "test": {
      "a": "a",
      "b": "b"
    }
  }
]

after dropping first function

DROP FUNCTION test(text, text);

and repeat request i have got correct response

{
  "a": "a",
  "b": "b"
}

[steve-chavez]

Hmm.. I cannot reproduce. I get:

[{"test":{
  "a": "a",
  "b": "b"
}}]

Consistently, on both 6.0.2 and master version. Even after DROP FUNCTION test(text, text);.

Not saying the above output is right though, I think the top key test shouldn't appear in the json.

[dwagin]

Hmm.. I cannot reproduce. I get:

[{"test":{
  "a": "a",
  "b": "b"
}}]

This is the bug

Consistently, on both 6.0.2 and master version. Even after DROP FUNCTION test(text, text);.

Not saying the above output is right though, I think the top key test shouldn't appear in the json.

The problem is that the type of return value is not determined correctly:
First function return composite type and returnsScalar must be false
Second function return scalar type and returnsScalar must be true

When both functions are present in the database call second function returnsScalar is false, but must be true.
When only second function are present in the database all fine.

[steve-chavez]

I can now reproduce. Here's a test case that could be put in our fixtures/schema.sql.

create type custom_type as (
  a text
, b text
);

create or replace function get_custom_type(a text, b text) returns custom_type
as $_$
  select row (a, b)::custom_type;
$_$ language sql;

create or replace function get_custom_type(json) returns json
as $_$
  select $1;
$_$ language sql;

When calling the latter function, we get:

curl 'l:3000/rpc/get_custom_type' \
-H 'Content-Type: application/json' \
-H 'Prefer: params=single-object' \
-d '{
  "a": "a",
  "b": "b"
}'

[{"get_custom_type":{
  "a": "a",
  "b": "b"
}}]     

And when dropping the former function drop function get_custom_type (text, text); we get the correct result without the wrapping get_custom_type.

For now, the workaround would be having the json param function like:

create or replace function get_custom_type(json) returns custom_type
as $_$
  select * from json_populate_record(null::custom_type, $1)
$_$ language sql;

This is kind of an edge case. As I think is not usual to change the return type of an overloaded function. Should definitely be corrected though.