PostgreSQL: syntax error at or near "refcursor"

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.

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.