Warn when a postgres procedure is returning a setof table result with an incorrect column order

If procedure is defined to return a setof table type, what can I do to ensure that columns from the procedure are being returned in the correct order.

Here is an example of the problem

https://dbfiddle.uk/AzJ5gPa7

The following function makes an assumption about the column order in the return table type of business_user_group

create or replace function organisation_groups(
    organisation_id varchar
) returns setof business_user_group as $$
  select
    business_user_group.organisation_id,
    business_user_group.group_id,
    business_user_group.business_user_id
  from business_user_group
  where business_user_group.organisation_id = organisation_groups.organisation_id;
$$ language sql stable;

If the column order is different for the table type business_user_group between deployed environments this becomes a real footgun.

DB diffing tools often miss column order, which doesn't help matters here.

What can be done to prevent this ?

The problem is with the select list in the query. When selecting into a list of variables in a PL/pgSQL procedure, the general recommendation is to explicitly enumerate the columns instead of using SELECT *. Barring programmer error, enumerating the columns ensures that the order is compatible between the select list and the variables regardless of column order in the table.

Your situation requires the opposite approach; i.e., use SELECT * to ensure that the returned data is consistent with the table's column order. Because the column order for both the type associated with the table and SELECT * are fully dependent on the table's column order, it is guaranteed that the order will match. This approach also reduces the amount of code maintenance required when columns are added, dropped, or renamed.

In a PL/pgSQL procedure when a query selects into a variable declared as the row type of the table, then the SELECT * approach should also be preferred.