PostgreSQL Reports

A report is a PL/pgSQL function that returns one cursor per result set. myDBR fetches the cursors in the order they are returned, so a report can have titles, charts, tables and tabs as on the other databases.

create or replace function sp_DBR_sales_by_region(inyear integer)
returns setof refcursor
language plpgsql as $$
declare
  c refcursor;
begin
  c := null; open c for select 'dbr.title', 'Sales ' || inyear; return next c;
  c := null; open c for select 'dbr.sum', 'Sales';                 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
    order by 2 desc;
  return next c;
end
$$;

For details of how myDBR calls functions, the kinds of functions and the case of names, see Using PostgreSQL Functions.

Example: a report with several result sets

The demo report sp_DBR_demo_city shows a subtitle, a page view layout, a table, a map and a map marker. Each of them is a separate result set, so each is returned as its own cursor.

CREATE OR REPLACE FUNCTION sp_dbr_demo_city(incityid integer)
RETURNS SETOF refcursor
LANGUAGE plpgsql AS $$
DECLARE
  c refcursor;
BEGIN
  -- 1. Subtitle command
  c := NULL; OPEN c FOR SELECT 'dbr.subtitle', 'Facts & Figures';
  RETURN NEXT c;

  -- 2. Page view command
  c := NULL; OPEN c FOR SELECT 'dbr.pageview';
  RETURN NEXT c;

  -- 3. The table. Double quoted aliases keep the case and the spaces of the headings.
  c := NULL; OPEN c FOR
    SELECT city.Name AS "City name",
           city.District AS "District",
           city.Population AS "City Population",
           country.Name AS "Country",
           country.Population AS "Country Population"
    FROM demo_city city
      JOIN demo_country country ON country.Code = city.CountryCode
    WHERE city.ID = incityid;
  RETURN NEXT c;

  -- 4. Map command
  c := NULL; OPEN c FOR
    SELECT 'dbr.openstreetmap', 'address', Name, 400, 400
    FROM demo_city
    WHERE ID = incityid;
  RETURN NEXT c;

  -- 5. Map marker: title and the HTML of the info window
  c := NULL; OPEN c FOR
    SELECT (city.Name || ', ' || country.Name),
           ('<b>' || city.Name || ', ' || country.Name || '</b><br><br>' ||
            'Population in ' || city.Name || ': ' || city.Population || '<br>' ||
            'District: ' || city.District)
    FROM demo_city city
      JOIN demo_country country ON country.Code = city.CountryCode
    WHERE city.ID = incityid;
  RETURN NEXT c;

  -- 6. The result sets of another report are returned as they are
  RETURN QUERY SELECT * FROM sp_demo_source('sp_DBR_demo_city');

  RETURN;
END
$$;

How the refcursors work

• A function that RETURNS SETOF refcursor returns a list of cursor names. myDBR reads the cursors in the order in which they were returned with RETURN NEXT, and each cursor is one result set. The order of the result sets is the order of the RETURN NEXT statements.
• One refcursor variable is enough. c := NULL; before OPEN c FOR ... makes PostgreSQL give each cursor a new unique name. Without it, the variable still holds the name of the previous cursor, and OPEN fails with the error cursor ... already in use.
• The values of the parameters and the variables are bound to the query when the cursor is opened, and the rows are read by myDBR after the function has returned, in the same transaction. The result of a cursor is therefore not affected by later changes to the variables. Because the rows are read after the function has returned, temporary tables the cursor reads must be created with ON COMMIT DROP (not dropped inside the function).
• RETURN QUERY SELECT * FROM sp_other(...) adds the cursors of another report function to the result. This is how sp_demo_source adds the source code block to the demo reports. Use PERFORM instead when the called function returns void.
• The final RETURN; is optional in a function that has returned its cursors with RETURN NEXT.
• A cursor that is not a report command is shown as a table. Commands (dbr.subtitle, dbr.pageview, dbr.openstreetmap and the other dbr.* commands) are cursors with one row whose first column is the command name, exactly as in a MySQL report.

Parameter names are lower case

PostgreSQL converts unquoted identifiers to lower case, so the parameter of the example is stored as incityid, even if you write inCityID. myDBR therefore handles all parameter names in lower case:

• The report and its parameter names are shown in lower case in the parameter list. Set the title of the parameter in the parameter settings of the report to show a readable text.
• Linked reports (dbr.report), editable reports (dbr.editable) and connected parameters are matched case insensitively, so inCityID=1 and incityid=1 both refer to the parameter.
• Use lower case names in the parameter settings and in your own calls to avoid confusion: select * from sp_dbr_demo_city(incityid => 12).
• The parameters that are named in... keep their meaning as automatic parameters (inLogin, inIPAddress, inLocale), and they are also compared without regard to case.

Rules

• Declare the function with returns setof refcursor. For each result set write c := null; open c for ...; return next c;. Setting c := null first gives every cursor its own name, so a single variable is enough.
• The values of dbr.* commands need no cast. A cast is only needed for a column that contains only null in a union, or for mixed types.
• Parameters are the arguments of the function. Write the names in lower case (inyear): PostgreSQL stores them in lower case, and the names used in dbr.report, dbr.editable and the parameter references are matched in lower case. The supported parameter types are integer, numeric, date, timestamp, time, varchar or text, and boolean. Set the titles in the parameter settings of the report. The automatic parameters (inLogin, inIPAddress, inLocale and so on) work as on the other databases.
• Aliases with capital letters, spaces or [ref] references must be double quoted: x as "Total sales", x as "Rate[rate]".
• A function that returns table(...) is also a report, with one result set. A function that returns void can be used as an update procedure of an editable report or an import.
• raise exception 'Message' shows only the message to the user, like SIGNAL on MySQL. For administrators, raise notice messages are shown after the report.
• Temporary tables: create temp table t on commit drop as select ....
• A report can return the result sets of another report: return query select * from sp_DBR_other(inyear);.
• The functions are created in the schema of the connection (search_path). Names are case insensitive.

Parameter queries

A parameter query is either a select statement or a function that returns a cursor, as a report does. The first column is the value, the second the displayed text. A third column named parameter_info shows an information text.

create or replace function sp_ADBR_car_models(inmanufacturer integer)
returns setof refcursor
language plpgsql as $$
declare
  c refcursor;
begin
  c := null;
  open c for
    select id, name, 'y', name || ' is a nice model' as parameter_info
    from car_models
    where manufacturer_id = inmanufacturer
    order by 1;
  return next c;
end
$$;

A multiselect parameter delivers a string such as 1,2,4. Split it with string_to_array(inlist, ','), for example where id::text = any (string_to_array(inlist, ',')).

Differences to MySQL syntax

MySQL	PostgreSQL
ifnull(a, b)	coalesce(a, b)
if(c, a, b)	case when c then a else b end
concat(a, b)	a || b (concat() skips NULLs)
limit 5, 10	limit 10 offset 5
group_concat(x)	string_agg(x, ',')
find_in_set(x, list)	x::text = any (string_to_array(list, ','))
date_format(d, '%Y-%m')	to_char(d, 'YYYY-MM')
year(d), month(d)	extract(year from d)::int
date_add(d, interval 1 day)	d + interval '1 day'
now()	localtimestamp(0) (now() has a time zone)
last_insert_id()	insert ... returning id into v
`name`	"name"
set @v = 1	a declared variable, v := 1
temporary tables dropped by hand	on commit drop

Data types

See Supported Data Types.