Postgrex bad decoding of data for stored procedures returning TABLE

Questions / Help
1

When using Postgrex (not ecto) I discovered that in the case of use stored procedures which return table its results are not correctly parsed.

Considering the following stored procedure that will return a TABLE with 2 elements as integers by example:

-- Test function for reporting
DROP FUNCTION IF EXISTS test();
CREATE FUNCTION test() RETURNS TABLE ( t INTEGER ) AS $$
	BEGIN
		RETURN QUERY SELECT 65;
		RETURN QUERY SELECT 666;
	
	END;
$$ LANGUAGE 'plpgsql';

When running in elixir code like: Postgrex.prepare_execute(pid(), "", "SELECT * FROM test()", []) |> IO.inspect I get the following result:

{:ok,
 %Postgrex.Query{
   cache: :reference,
   columns: ["t"],
   name: "",
   param_formats: [],
   param_oids: [],
   param_types: [],
   ref: #Reference<0.1261069667.1929904133.52189>,
   result_formats: [:binary],
   result_oids: [23],
   result_types: [Postgrex.Extensions.Int4],
   statement: "SELECT * FROM test()",
   types: {Postgrex.DefaultTypes, #Reference<0.1261069667.1930035205.52173>}
 },
 %Postgrex.Result{
   columns: ["t"],
   command: :select,
   connection_id: 11665,
   messages: [],
   num_rows: 2,
   rows: ['A', [666]]
 }}

Looking at the last line rows it is clear that the integers returned from stored procedure’s table are not integers but taken as chars (A → 65 for ASCII, and [666] is just outside ASCII’s scope).

The expected result returned would be more like: rows: [65, 666] instead.

EDIT: Looking at result_oids: [23] I can see that it detects correctly output’s type, since 23 is INTEGER oid.

Is this a bug of not handled properly those stored procedures RETURNING TABLE or am I doing something wrong?

Thanks for the attention :wink:

2

IO.inspect/2 is printing the [65] as a charlist .

This behaviour can be changed by explicitly passing the :charlists option.
Like this |> IO.inspect(charlists: :as_lists).

See Inspect.Opts to find all the available options.

2 Likes
3

Thanks for the reply!

It’s strange enough as this only happens in this case, not in regular tables so it was driving me crazy!