Tutorial: Converting Postgres types

In this tutorial we will convert string representations of Postgres types to PHP values and back, configuring the converters as needed to properly handle the input.

Complex type representations in Postgres

Postgres supports complex data types that can be combined in creative ways. We’ll first explore the two ways used to represent values for such types.

Let’s connect to any database (e.g. the default postgres one) using psql console client and execute the following query

select row(
    array['string', 'str''i"ng'],
    point(55.75, 37.61),
    daterange('2014-09-28', '2025-02-25'),
    age('2025-02-25', '2014-09-28')
) as complex;

We are creating a row (composite) type literal with its fields being an array of text, geometric point, range of dates and an interval (generated by a function). The values for complex types in the query are represented by “constructors”, so the query itself looks readable. However, its output will look somewhat like this:

                                             complex
--------------------------------------------------------------------------------------------------
 ("{string,""str'i\\""ng""}","(55.75,37.61)","[2014-09-28,2025-02-25)","10 years 4 mons 27 days")

Note that your output may, in fact, look different. That’s because the representation of datetime and interval fields may change based on DateStyle and IntervalStyle server settings, respectively. Let’s change these to some fixed values with the following statements

set DateStyle = 'postgres,dmy';
set IntervalStyle = 'sql_standard';

and re-run the query that returns the row literal:

                                           complex
---------------------------------------------------------------------------------------------
 ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")

This time you should get exactly the above output. What we see here is the string representation of composite type (elements enclosed in parentheses and separated by commas) containing string representations of its fields. Note the quoting and escaping that was applied, especially to the second element of the array.

Converting string literals to PHP values

Both PHP extensions that are used for talking to PostgreSQL (pgsql and PDO_pgsql) will return the same string representation for the complex field if the above query is run through them.

Let’s take that string representation and convert it using the classes available in pg_wrapper package. We will use the DefaultTypeConverterFactory class to create the necessary converter and then pass the string to it:

use sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory;

$converter = (new DefaultTypeConverterFactory())->getConverterForTypeSpecification([
    'one'   => 'text[]',
    'two'   => 'point',
    'three' => 'daterange',
    'four'  => 'interval'
]);
$complex = $converter->input(<<<'LITERAL'
    ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")
    LITERAL
));

getConverterForTypeSpecification() method takes a manually prepared specification for some database type and returns an appropriate TypeConverter implementation. In this case the type specification for a composite type is an array:

  • Its keys will be used in the resultant array. As you can see above, composite type literals in Postgres do not contain any info on field names.

  • Its values specify types for fields of the composite type, these specifications are simply type names.

Note also the text[] specification for the first field containing an array.

The above code, however, will fail with a TypeConversionException: the DateConverter instance used for converting daterange expects a date in ISO format by default. Normally it can use the database connection to properly set up the input format, but this time we’ll configure it manually:

use sad_spirit\pg_wrapper\converters\containers\RangeConverter;
use sad_spirit\pg_wrapper\converters\datetime\DateConverter;
use sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory;

$dateConverter = new DateConverter();
$dateConverter->setDateStyle('Postgres,dmy');

$converter = (new DefaultTypeConverterFactory())->getConverterForTypeSpecification([
    'one'   => 'text[]',
    'two'   => 'point',
    'three' => new RangeConverter($dateConverter),
    'four'  => 'interval'
]);

$complex = $converter->input(<<<'LITERAL'
    ("{string,""str'i\\""ng""}","(55.75,37.61)","[28-09-2014,25-02-2025)","+10-4 +27 +0:00:00")
    LITERAL
);
print_r($complex);

As you may notice, the converters are combined in pretty much the same way as the types themselves. Thus adding a converter for a new base type will immediately allow using it for complex types (arrays, ranges, composites) containing this base type.

Now the code works as expected, outputting the following (some parts are omitted for brevity):

Array
(
    [one] => Array
        (
            [0] => string
            [1] => str'i"ng
        )

    [two] => sad_spirit\pg_wrapper\types\Point Object
        (
            [x] => 55.75
            [y] => 37.61
        )

    [three] => sad_spirit\pg_wrapper\types\DateTimeRange Object
        (
            [lower] => DateTimeImmutable Object
                (
                    [date] => 2014-09-28 00:00:00.000000
                    ...
                )
            ...
        )

    [four] => DateInterval Object
        (
            [y] => 10
            [m] => 4
            [d] => 27
            ...
        )

)

Note how the outer composite type is converted to an associative array with the keys from type specification, the array on Postgres side is mapped to array on PHP side. Postgres dates and intervals are represented by native DateTimeImmutable and DateInterval native classes, while types that do not have native equivalents are represented by Point and DateTimeRange classes from the package.

Converting PHP values to string literals

While it is possible to use constructor expressions for complex types when writing queries manually, you can’t use these for parameters in parametrized queries. Fortunately, type converters implement an output() method that complements input() and allows generating string literals from PHP values. You can pass these to pg_execute(), pg_query_params(), and similar methods.

Let’s continue the above example

use sad_spirit\pg_wrapper\types\DateTimeRange;

$complex['one'] = [['a', 'b'], ['c', 'd"e']];
// DateTimeRange is immutable, create a new instance
$complex['three'] = new DateTimeRange(
    $complex['three']->lower,
    $complex['three']->upper,
    true,
    true
);

echo $converter->output($complex);

This prints

("{{""a"",""b""},{""c"",""d\\""e""}}","(55.75,37.61)","[""2014-09-28"",""2025-02-25""]","P10Y4M27D")

Note that date and time converters always output in ISO format to prevent ambiguity.

This concludes the tutorial, we have used the type conversion classes on a complex type composed of the built-in types. The next chapters contain the complete API reference for the converters and describe the means available to convert custom base types.