PostgreSQL

myDBR can use PostgreSQL 12 or later (14 or later is recommended) for its own objects and for the reports, in the same way as MySQL, MariaDB and Microsoft SQL Server. The report logic is written as PL/pgSQL functions that return one cursor for each result set. See PostgreSQL Reports for how to write them.

Requirements

Item	Requirement
PostgreSQL	12 or later
PHP	The pgsql extension (php -m | grep pgsql)
PostgreSQL extension	citext (part of the contrib package). It is trusted since PostgreSQL 13, so the owner of the database can create it.

The citext type makes names, user names and report names case insensitive, as they are on MySQL and SQL Server. The installation script runs CREATE EXTENSION IF NOT EXISTS citext.

Installation

1. Open the installation page and select PostgreSQL as the database type (default port 5432).
2. If the user or the database does not exist, enter the credentials of a PostgreSQL administrator and use Create User. The wizard creates the role and a database owned by it.
3. Optional: enter a Schema (for example mydbr) to install the myDBR objects into their own schema of an existing database, apart from your own data.
4. Continue with Create Objects. The installer runs mydbr_create_pgsql.sql as a single transaction, so a failure leaves nothing half installed.
5. Optionally create the demo reports.

The default administrator is dba with the password dba. Change it after the first login.

A PostgreSQL connection is bound to one database and queries cannot join across databases, so install myDBR into a schema of the database that holds your data. This is the recommended way. The commands for the role, the schema, the grants, the creation script, the configuration file (DB_SCHEMA) and the search path are in Installing myDBR on PostgreSQL. See also Quick Start and Manual Installation.

Features of PostgreSQL

These features use what is special in PostgreSQL and are available on PostgreSQL only.

Feature	What it does
Row-Level Security	The database decides which rows a user sees. myDBR passes the user, or runs the report as the database role of the user.
Report Timeout and Running Reports	PostgreSQL stops reports that run too long. An administrator sees the running reports and can cancel them.

Tools

• SQL Editor: statements are separated with go on its own line. New reports start from a function template. When a report is saved with a different parameter list, the old function is removed, so no ambiguous overloads remain.
• Data Browser and Query Builder: the "databases" are the schemas of the connected database.
• Synchronization: the script uses insert ... on conflict. PostgreSQL keeps no creation time for functions, so the date based selection includes all reports and routines. Select by folder or name to limit the set.
• Version control (Git): all reports, functions and views are listed on each run, because there are no modification times.
• AI Report Assistant: writes PL/pgSQL reports with cursors and uses PostgreSQL examples.

Known limitations

• A connection is bound to one database. Cross-database queries are not possible in PostgreSQL, and the Data Browser shows schemas instead.
• A report that fails with an exception returns none of its result sets, because the function is aborted as a whole.
• Parameter names are shown in lower case, as PostgreSQL stores them in lower case.
• The SQL Editor Processes button (SHOW PROCESSLIST) is not available. The Running reports page of the administration menu lists the reports that are running.
• Names are case insensitive. Two objects that differ only by case are not supported.