Edit

#Type Query Builder

• Back

The Type Query Builder provides a fluent, type-safe interface for constructing PostgreSQL type management statements: CREATE TYPE (composite, enum, range), ALTER TYPE, and DROP TYPE.

#CREATE TYPE (Composite)

#Basic Composite Type

<?php

use function Flow\PostgreSql\DSL\{create, type_attr};

$query = create()->compositeType('address')
    ->attributes(
        type_attr('street', 'text'),
        type_attr('city', 'text'),
        type_attr('zip', 'text')
    );

echo $query->toSQL();
// CREATE TYPE address AS (street text, city text, zip text)

#With Schema

<?php

use function Flow\PostgreSql\DSL\{create, type_attr};

$query = create()->compositeType('public.address')
    ->attributes(
        type_attr('street', 'text')
    );

echo $query->toSQL();
// CREATE TYPE public.address AS (street text)

#With Collation

<?php

use function Flow\PostgreSql\DSL\{create, type_attr};

$query = create()->compositeType('person')
    ->attributes(
        type_attr('name', 'text')->collate('en_US')
    );

echo $query->toSQL();
// CREATE TYPE person AS (name text COLLATE "en_US")

#CREATE TYPE (Enum)

#Basic Enum Type

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->enumType('status')
    ->labels('pending', 'active', 'closed');

echo $query->toSQL();
// CREATE TYPE status AS ENUM ('pending', 'active', 'closed')

#With Schema

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->enumType('public.status')
    ->labels('pending', 'active');

echo $query->toSQL();
// CREATE TYPE public.status AS ENUM ('pending', 'active')

#CREATE TYPE (Range)

#Basic Range Type

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('floatrange')
    ->subtype('float8');

echo $query->toSQL();
// CREATE TYPE floatrange AS RANGE (subtype = float8)

#With Schema

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('public.floatrange')
    ->subtype('float8');

echo $query->toSQL();
// CREATE TYPE public.floatrange AS RANGE (subtype = float8)

#With Subtype Operator Class

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('floatrange')
    ->subtype('float8')
    ->subtypeOpclass('float8_ops');

echo $query->toSQL();
// CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_opclass = 'float8_ops')

#With Collation

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('textrange')
    ->subtype('text')
    ->collation('en_US');

echo $query->toSQL();
// CREATE TYPE textrange AS RANGE (subtype = text, "collation" = 'en_US')

#With Canonical Function

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('daterange')
    ->subtype('date')
    ->canonical('daterange_canonical');

echo $query->toSQL();
// CREATE TYPE daterange AS RANGE (subtype = date, canonical = 'daterange_canonical')

#With Subtype Diff Function

<?php

use function Flow\PostgreSql\DSL\create;

$query = create()->rangeType('floatrange')
    ->subtype('float8')
    ->subtypeDiff('float8mi');

echo $query->toSQL();
// CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = 'float8mi')

#ALTER TYPE (Enum)

#Add Value

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->enumType('status')
    ->addValue('archived');

echo $query->toSQL();
// ALTER TYPE status ADD VALUE 'archived'

#Add Value IF NOT EXISTS

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->enumType('status')
    ->addValue('archived')
    ->ifNotExists();

echo $query->toSQL();
// ALTER TYPE status ADD VALUE IF NOT EXISTS 'archived'

#Add Value BEFORE

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->enumType('status')
    ->addValueBefore('pending', 'active');

echo $query->toSQL();
// ALTER TYPE status ADD VALUE 'pending' BEFORE 'active'

#Add Value AFTER

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->enumType('status')
    ->addValueAfter('archived', 'closed');

echo $query->toSQL();
// ALTER TYPE status ADD VALUE 'archived' AFTER 'closed'

#Rename Value

<?php

use function Flow\PostgreSql\DSL\alter;

$query = alter()->enumType('status')
    ->renameValue('old_name', 'new_name');

echo $query->toSQL();
// ALTER TYPE status RENAME VALUE 'old_name' TO 'new_name'

#DROP TYPE

#Simple Drop

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address');

echo $query->toSQL();
// DROP TYPE address

#IF EXISTS

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address')
    ->ifExists();

echo $query->toSQL();
// DROP TYPE IF EXISTS address

#CASCADE

Drop all objects that depend on the type:

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address')
    ->cascade();

echo $query->toSQL();
// DROP TYPE address CASCADE

#RESTRICT

Refuse to drop the type if any objects depend on it (default behavior):

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address')
    ->restrict();

echo $query->toSQL();
// DROP TYPE address

#Multiple Types

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address', 'status', 'floatrange');

echo $query->toSQL();
// DROP TYPE address, status, floatrange

#Combined Options

<?php

use function Flow\PostgreSql\DSL\drop;

$query = drop()->type('address')
    ->ifExists()
    ->cascade();

echo $query->toSQL();
// DROP TYPE IF EXISTS address CASCADE

For a complete list of DSL functions, see the DSL reference.