PgsqlSqlUtilBase Module Introduction
The PgsqlSqlUtilBase module provides a high level API for working with PostgreSQL database objects and provides the implementation for the jdbc and odbc SqlUtil modules supporting PostgreSQL.
Usually this module is loaded on demand from the SqlUtil module, however to use this module directly, use "%requires PgsqlSqlUtilBase" in your code.
All the public symbols in the module are defined in the PgsqlSqlUtilBase namespace
Schema Management on PostgreSQL
Type Mapping
Column types are mapped from Qore types as follows:
PostgreSQL Column Type Mappings
To use other types, use the "native_type" column description option with the native PostgreSQL type name instead (under the "driver" and "pgsql" keys for schemas supporting multiple databases).
Additional Object Types Supported
The following additional schema objects can be managed with PgsqlSqlUtilBase:
Types
The Schema Description Hash takes an optional key, "types" that allows types in PostgreSQL schemas to be managed along with other objects.
The "types" should be assigned to a hash, each key name is the name of the type, and the values are strings giving the type definition.
The "types" key can go in the top level of the Schema Description Hash for PostgreSQL-only schemas, or, for schemas targeting multiple database types, under the "driver" and "pgsql" keys as in the following example:
hash<auto> schema = {
"driver": {
"pgsql": {
"types": {
"info_record": "id numeric, ind numeric, status char",
},
},
},
};
Function Handling
- Function Names and Matching
- PostgreSQL functions are identified by their name and arguments; the algorithm implemented in this module is fairly simple; the argument names and types are used when trying to find functions, so they must match almost exactly.
The only exception to the exact matching algorithm is that some simple substitutions are done on user-supplied arguments to try and use the verbose type names as retrieved by pg_catalog.pg_get_function_identity_arguments(), which always returns verbose type names (ie "character varying" instead of "varchar").
Substitutions are made according to the inverse value of PgsqlSqlUtilBase::PgsqlTable::PgsqlNameMap.
This is done so that a user-supplied function name like "update_queue(qkey varchar, qdata text)" will match the value returned by pg_catalog.pg_get_function_identity_arguments(), which would be "update_queue(character varying queue)"
- Schema Management with Functions
- There is some simple logic in the driver that attempts to reorder function definitions so that they match the format returned by pg_catalog.pg_get_functiondef() which requires the following string to follow the function name and arguments:
returns type language lang as $function$
The logic in the module attempts to move any language and volatility declarations from the end of the declaration to the beginning. Additionally, dollar-quoting is detected automatically and substituted with "$function$" automatically.
If your function is reported as needing updating even though it's already in the database correctly, then the logic in this module needs to be update to provide more flexibility (please raise an issue here: https://github.com/qorelanguage/qore/issue). Until the module can be enhanced, try the format as in the following example: update_queue_data(qkey varchar, qdata text) returns integer language plpgsql as $function$
declare
rowcount integer;
begin
update queue_data set data = qdata, queue_data_status = 'R' where queuekey = qkey and queue_data_status = 'W';
get diagnostics rowcount = row_count;
return rowcount;
end;
$function$
- Note
- Future versions of the module may provide more intelligence to reformat user-supplied functions to match the format retrieved from the database, however currently functions should be provided in the format above in order to use functionality like AbstractDatabase::getAlignFunctionSql() for example.
Release Notes
PgsqlSqlUtilBase v1.8
- updated to support
jdbc and odbc drivers as well as the native pgsql driver
PgsqlSqlUtilBase v1.7.1
- fixed bugs handling table objects in schemas other than
public (issue 4602)
PgsqlSqlUtilBase v1.7
- implemented support for generating queries based on generic expressions using the DataProvider module's generic expression support (issue 4538)
PgsqlSqlUtilBase v1.6
- added supoport for the
XML, JSON, and JSONB types (issue 4448)
PgsqlSqlUtilBase v1.5.3
- fixed handling column names that use reserved words (issue 4348)
PgsqlSqlUtilBase v1.5.3
- added APIs to enable duplicate record creation errors to be detected (issue 4308)
PgsqlSqlUtilBase v1.5.3
- fixed bugs dropping foreign constraints and indices when aligning tables (issue 4246)
PgsqlSqlUtilBase v1.5
PgsqlSqlUtilBase v1.4.1
- fixed a bug generating literal date/time values for SQL queries (issue 3948)
PgsqlSqlUtilBase v1.4
PgsqlSqlUtilBase v1.3.1
- fixed compatibility with PostgreSQL 12 (issue 3589)
- fixed comparison of triggers with column restrictions with table alignment (issue 3466)
- fixed quoting of reserved words in column names in table alignment (issue 3400)
PgsqlSqlUtilBase v1.3
PgsqlSqlUtilBase v1.2.1
- implemented support for custom column operators (issue 2314)
- fixed a bug where default column values were compared incorrectly leading to false positives when comparing and aligning DB schemas (issue 2527)
PgsqlSqlUtilBase v1.2
- updated for complex types
PgsqlSqlUtilBase v1.1.2
- fixed a bug in setting a
comment for a table column (issue 1886)
- implemented cop_trunc_date() operator (issue 2032)
PgsqlSqlUtilBase v1.1.1
- implemented the force option (i.e. cascade) for dropping code objects (issue 1314)
PgsqlSqlUtilBase v1.1
- added support for
cop_cast operator (feature 535)
- added support for the following datatypes and aliases: bool, float, int, timetz, timestamptz, varbit
- fixed support for the following datatypes and aliases: bit, bit varying, char, character, character varying, char varying, oid, varchar
- fixed bugs handling "bigint" and "double precision" column types with schema alignments
- fixed a bug with PgsqlTable::tryInsertImpl(); added an explicit "begin" call to make the savepoint work with PostgreSQL 9.3+ servers
- fixed a bug retrieving foreign constraints; columns were not guaranteed to be returned in declaration order
- implemented support for the
"returning" clause as an insert option
- fixed a bug handling tablespaces in unique constraints
- fixed bugs in SqlUtil::cop_seq() and SqlUtil::cop_seq_currval() (issue 624)
- added support for listing PostgreSQL types and materialized views (issue 699)
PgsqlSqlUtilBase v1.0
