Using PostgreSQL Functions

PostgreSQL does not have stored procedures that return result sets. A myDBR report, a parameter query and an update procedure are all functions. This page explains how myDBR calls them, which kinds of functions you can write, and where the case of names matters.

How myDBR calls a function

When a report is run, myDBR turns the report name and its parameters into a select statement:

select * from sp_DBR_sales_by_region(2024)

If the function returns setof refcursor, myDBR opens a transaction, collects the cursor names and fetches the rows of each cursor in order (FETCH ALL). Each cursor is one result set. The transaction is committed when the report has been read.

You can test a report in psql in the same way:

begin;
select * from sp_DBR_sales_by_region(2024);   -- returns the cursor names
fetch all from "<unnamed portal 1>";          -- the first result set
fetch all from "<unnamed portal 2>";          -- the second result set
rollback;

Kinds of functions

Return type	Used as	Result sets
setof refcursor	Report with any number of result sets, or a parameter query	One for each cursor
table(...) or setof a row type	Report or parameter query with one result set	One
void	Update procedure of an editable report, import procedure, an action	None
a scalar type	Report with a single value	One row, one column

Reports with several result sets

create or replace function sp_DBR_summary(inyear integer)
returns setof refcursor
language plpgsql as $$
declare
  c refcursor;
begin
  c := null; open c for select 'dbr.title', 'Summary ' || inyear; return next c;

  c := null; open c for select region as "Region", sum(amount) as "Sales"
                        from sales where extract(year from sold_at) = inyear group by region;
  return next c;
end
$$;

Reuse the one c variable and set it to null before each open. A cursor opened with a null variable gets an automatically generated unique name, so the earlier cursors stay open until they have been read.

Reports with one result set

A report with one result set needs no cursor:

create or replace function sp_DBR_top_customers(inlimit integer)
returns table (customer varchar, total numeric)
language sql as $$
  select name, sum(amount) from orders group by name order by 2 desc limit inlimit;
$$;

Update procedures

create or replace function sp_DBR_budget_update(inid integer, invalue integer)
returns void
language plpgsql as $$
begin
  update budget set value = invalue where id = inid;
end
$$;

An update procedure of the dbr.editable command can also return a result set (a JSON object with the new value and style). Return a cursor with one row in that case.

Parameters

• The function arguments are the report parameters, in the order in which they are declared.
• Use parameter names that do not collide with column names. Prefix them with in, as in inyear, inregion. A name that is the same as a column gives the error column reference is ambiguous.
• Default values are not used by myDBR. Define the default of a parameter in the parameter settings of the report.
• Overloads: if a function with the same name has several argument lists, myDBR cannot choose between them. Keep one function for each report name. The SQL Editor drops the old version when you save a report with a changed parameter list.

Errors and messages

• raise exception 'Message'; stops the report and shows the message to the user, like SIGNAL on MySQL.
• raise notice 'text'; messages are shown to administrators after the report.
• A report that raises an exception returns none of its result sets. If you want to show a message together with other results, return it as a normal result set (for example select 'No data for this year') instead of raising an exception.

Case sensitivity

PostgreSQL treats the case of identifiers differently from MySQL and SQL Server. In most cases it does not matter, and myDBR hides the differences, but the following rules apply.

Names of functions, tables, columns and variables

PostgreSQL converts every unquoted identifier to lower case. sp_DBR_Sales, SP_DBR_SALES and sp_dbr_sales are all stored as sp_dbr_sales. Therefore:

• Write function names without double quotes. A quoted name such as "sp_DBR_Sales" is stored with its capital letters, and the unquoted call that myDBR makes does not find it.
• The report name in the report list (mydbr_reports.proc_name) is case insensitive, so sp_DBR_Sales in the list finds the function sp_dbr_sales.
• Two functions or tables whose names differ only by case are not supported. You cannot have both sp_DBR_Sales and sp_DBR_sales.
• The same applies to your own tables and columns. A table created as "Customers" (quoted) must be written as "Customers" in every query. Unquoted Customers means customers. Prefer lower case names in your own schema.

Parameter names

Parameter names are stored in lower case. myDBR matches the names used by dbr.report, dbr.editable, dbr.form and the connected parameters case insensitively, but the names are shown in lower case in the parameter list and in error messages. If you want a nicer text for the user, set the parameter title in the parameter settings of the report.

Column headings

An unquoted column alias is converted to lower case, and spaces and special characters are not allowed. To keep the case, to use spaces, or to use the myDBR notations in the heading, put the alias in double quotes:

select amount as total_sales,       -- heading: total_sales
       amount as "Total sales",     -- heading: Total sales
       rate   as "Rate[rate]",      -- column reference
       d      as "Date:type=date"   -- column data type

Comparing text

Where	Comparison
myDBR tables (mydbr_reports, mydbr_userlogin and so on)	Case insensitive (the citext type)
Your own varchar and text columns	Case sensitive, unlike the default of MySQL and SQL Server
Your own citext columns	Case insensitive when both sides are citext

If your report searched with a case insensitive match on MySQL, you must write it explicitly on PostgreSQL:

where name ilike '%' || insearch || '%'      -- instead of: name like concat('%', inSearch, '%')
where lower(name) = lower(inname)            -- exact match without regard to case

Another option is to declare the parameter as citext. Note that a citext column compared with a varchar or text value is case sensitive; both sides must be citext, or one side must be a citext and the other an untyped literal.

group by, distinct and order by also use the collation of the column. The sort order of text depends on the collation of the database (for example en_US.UTF-8), so it can differ from MySQL.

Quick checklist

1. Create the functions and tables with unquoted lower or mixed case names.
2. Use quoted aliases for headings that need capitals, spaces or myDBR notations.
3. Use ilike, lower() or citext when the search must ignore the case.
4. Do not create two objects whose names differ only by case.