2

When I work with Microsoft SQL Server databases, I sometimes return multiple result sets from stored procedures. I often return so many that it becomes hard to follow which is which. To solve this problem, I follow a convention I learned from a colleague: The 1st result set is a "map", which defines names for the 2nd and other result sets. It has a single record, where every field name is the name of a result set and the corresponding field value is its index in the returned array of result sets. Client code accesses specific result sets by finding out the index by name first.

The following simple example shows the idea:

create or alter procedure divide
  @a int,
  @b int
as
begin
  declare
    @error int = 0

  -- Name-to-index map
  select
    1 as result,
    2 as error

  -- Result
  if @b = 0
  begin
    set @error = 1
    select
      null as result
  end
  else
  begin
    select
      @a / @b as result
  end

  -- Error
  select
    @error as error
end

In this example, the first result set (index: 0) gives that there are 2 more result sets: one called "result" (index: 1) and another called "error" (index: 2). Both contain only one record: the result of the division and the error code, respectively.

Example call #1:

exec divide @a = 91, @b = 13

Result sets in JSON format:

[
  [{ result: 1, error: 2 }],
  [{ result: 7 }],
  [{ error: 0 }]
]

Example call #2:

exec divide @a = 91, @b = 0

Result sets in JSON format:

[
  [{ result: 1, error: 2 }],
  [{ result: null }],
  [{ error: 1 }]
]

I tried to port this technique to PostgreSQL 14 using the official documentation and especially this page. I came up with this:

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language sql as
$$
  declare
    ref1 refcursor;
    ref2 refcursor;
    ref3 refcursor;
    error int := 0;

  begin
    -- Name-to-index map
    open ref1 for select
      1 as result,
      2 as error;
    return next ref1;

    -- Result
    if b = 0 then
      error := 1;
      open ref2 for select
        null as result;
    else
      open ref2 for select
        a / b as result;
    end if;
    return next ref2;

    -- Error
    open ref3 for select
      error;
    return next ref3;
  end;
$$;

Unfortunately, I get an error: syntax error at or near "refcursor", referring to the refcursor in the 1st line after declare.

kol
  • 27,881
  • 12
  • 83
  • 120
  • 1
    Mimicking SQL Server approaches in Postgres is going to give you a lot of trouble - especially when procedures are (mis)used to return arbitrary and multiple results. Postgres simply works differently. Save yourself a lot of headaches and just run three queries and put them together with a UNION ALL or whatever suits you. Then create a _function_ that returns a table. –  Nov 26 '21 at 12:18
  • @a_horse_with_no_name The result sets I return are logically connected (e.g. displayed on the same web page), but have different structure. By returning all in a single function call I'd like to exploit that Postgres functions run in transaction, so the result will give me a consistent set of data. If I used multiple selects without a transaction, and there were an update between my selects, I could get an inconsistent result. (I hope I don't misunderstand something fundamental here -- I'm not a DB expert.) – kol Nov 26 '21 at 12:39
  • I am not entirely sure that all queries in a procedure or function see the same snapshot. I would expect that you can still have inconsistent results. The only way you can guarantee consistency is to run all queries with `repeatable read` or `serializable` isolation level. –  Nov 26 '21 at 12:42
  • @a_horse_with_no_name Hmm... looks like the default isolation level in PostgreSQL is only `read committed`. Moreover, contrary to the MS SQL version, the Postgres function returns only cursors, so I must fetch the records themselves in a transaction. – kol Nov 26 '21 at 13:06
  • 1
    `read committed` only guarantees consistent results for a single statement, not for a transaction. And as I wrote: I doubt that all queries inside a procedure are considered a single statement - but there are people more knowledgeable that me with the internals of that. I am only speculating –  Nov 26 '21 at 13:15

1 Answers1

2

You used the wrong language declaration. Your procedure is in plpgsql but you declared it as plain sql through language sql statement at the top.

Replacing

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language sql as

with

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language plpgsql as

Solves the problem.

Zegarek
  • 6,424
  • 1
  • 13
  • 24