PgsqlSqlUtil Module

Table of Contents

• PgsqlSqlUtil Module Introduction
• Schema Management on PostgreSQL
  • Type Mapping
  • Additional Object Types Supported
  • Types
  • Function Handling
• Release Notes
  • PgsqlSqlUtil v1.1
  • PgsqlSqlUtil v1.0

PgsqlSqlUtil Module Introduction

The PgsqlSqlUtil module provides a high level API for working with PostgreSQL database objects.

Usually this module is loaded on demand from the SqlUtil module, however to use this module directly, use "%requires PgsqlSqlUtil" in your code.

All the public symbols in the module are defined in the PgsqlSqlUtil namespace

Note

This module requires the pgsql binary module for communication with PostgreSQL databases

Schema Management on PostgreSQL

Type Mapping

Column types are mapped from Qore types as follows:

PostgreSQL Column Type Mappings

Generic Type Name	PostgreSQL Type Used
float	double
integer	bigint
number	numeric
string	varchar
date	timestamp
binary	bytea
bool	boolean
SqlUtil::BLOB	bytea
SqlUtil::CHAR	char
SqlUtil::CLOB	text
SqlUtil::NUMERIC	numeric
SqlUtil::VARCHAR	varchar

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 PgsqlSqlUtil:

• types

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:

my hash $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 PgsqlSqlUtil::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 report to david.nosp@m.@qor.nosp@m.e.org). 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 SqlUtil::Database::getAlignFunctionSql() for example.

Release Notes

PgsqlSqlUtil v1.1

• fixed bugs handling "bigint" and "double precision" column types with schema alignments

PgsqlSqlUtil v1.0

• initial release