The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DB-Object-v1.4.0
River stage zero No dependents
2 ++
/ DB::Object::Postgres::Tables

NAME

DB::Object::Postgres::Tables - PostgreSQL Table Object

SYNOPSIS

    use DB::Object::Postgres::Tables;
    my $this = DB::Object::Postgres::Tables->new || 
        die( DB::Object::Postgres::Tables->error, "\n" );

VERSION

    v1.0.0

DESCRIPTION

This is a PostgreSQL table object class. It inherits from DB::Object::Tables

METHODS

check

Sets or gets an hash reference of check constraint name to an hash of properties for that constraint.

See each driver for the value provided, but available properties typically are:

  • expr

    The check constraint expression

  • fields

    The array object of table columns associated with this check constraint.

  • name

    The check constraint name.

create

This creates a table.

It takes some array reference data containing the columns definitions, some optional parameters and a statement handler.

If a statement handler is provided, then no need to provide an array reference of columns definition. The columns definition will be taken from the statement handler. However, at least either one of them needs to be provided to set the columns definition.

Possible parameters are:

  • comment

  • inherits

    Takes the name of another table to inherit from

  • on commit

  • tablespace

  • temporary

    If provided, this will create a temporary table.

  • with oids

    If true, this will enable table oid

  • without oids

    If true, this will disable table oid

This will return an error if the table already exists, so best to check beforehand with "exists".

Upon success, it will return the new statement to create the table. However, if "create" is called in void context, then the statement is executed right away and returned.

create_info

This returns the create info for the current table object as a string representing the sql script necessary to recreate the table.

disable_trigger

    my $sth = $tbl->disable_trigger;
    my $sth = $tbl->disable_trigger( all => 1 );
    my $sth = $tbl->disable_trigger( name => 'my_trigger' );

Provided with some optional parameters and this will return a statement handler to disable all triggers or a given trigger on the table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

    $tbl->disable_trigger;
    # would issue immediately the following query:
    ALTER TABLE my_table DISABLE TRIGGER USER

It takes the following options:

  • all

    If true, this will disable all trigger on the table. Please note that, as per the PostgreSQL documentation this requires super user privilege.

    If false, this will disable only the user triggers, i.e. not including the system ones.

  • name

    If a trigger name is provided, it will be used to specifically disable this trigger.

drop

This will prepare a drop statement to drop the current table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

It takes the following options:

  • cascade

    If true, CASCADE will be added to the DROP query.

  • if_exists

    If true, this will add a IF EXISTS to the DROP query.

    You can also use if-exists

  • restrict

    If true, RESTRICT will be added to the DROP query.

See PostgreSQL documentation for more information

enable_trigger

    my $sth = $tbl->enable_trigger;
    my $sth = $tbl->enable_trigger( all => 1 );
    my $sth = $tbl->enable_trigger( name => 'my_trigger' );

Provided with some optional parameters and this will return a statement handler to enable all triggers or a given trigger on the table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

    $tbl->enable_trigger;
    # would issue immediately the following query:
    ALTER TABLE my_table ENABLE TRIGGER USER

It takes the following options:

  • all

    If true, this will enable all trigger on the table. Please note that, as per the PostgreSQL documentation this requires super user privilege.

    If false, this will enable only the user triggers, i.e. not including the system ones.

  • name

    If a trigger name is provided, it will be used to specifically enable this trigger.

exists

Returns true if the current table exists, or false otherwise.

foreign

Sets or gets an hash reference of foreign key constraint name to an hash of properties for that constraint.

Available properties are:

  • expr

    The foreign key expression used when creating the table schema.

  • match

    Typical value is full, partial and simple

  • on_delete

    The action the database is to take upon deletion. For example: nothing, restrict, cascade, null or default

  • on_update

    The action the database is to take upon update. For example: nothing, restrict, cascade, null or default

  • table

    The table name of the foreign key.

  • fields

    The associated table column names for this foreign key constraint.

  • name

    The foreign key constraint name.

lock

This will prepare a query to lock the table and return the statement handler. If it is called in void context, the statement handler returned is executed immediately.

It takes an optional lock type and an optional NOWAIT parameter.

Supported lock types are:

ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

See PostgreSQL documentation for more information

on_conflict

A convenient wrapper to "on_conflict" in DB::Object::Postgres::Query

optimize

Not implemented in PostgreSQL.

parent

This will return the parent table if the current table inherits from another table.

qualified_name

This return a fully qualified name to be used as a prefix to columns in queries.

If "prefixed" in DB::Object::Tables is greater than 2, the database name will be added.

If there is a schema defined and the "prefixed" in DB::Object::Tables is greater than 1, the schema will be added.

At minimum, the table name is added.

    $tbl->prefixed(2);
    $tbl->qualified_name;
    # Would return something like: mydb.my_schema.my_table

    $tbl->prefixed(1);
    $tbl->qualified_name;
    # Would return only: my_table

rename

Provided with a new table name, and this will prepare the necessary query to rename the table and return the statement handler.

If it is called in void context, the statement handler is executed immediately.

    # Get the prefs table object
    my $tbl = $dbh->pref;
    $tbl->rename( 'prefs' );
    # Would issue a statement handler for the query: ALTER TABLE pref RENAME TO prefs

See PostgreSQL documentation for more information

repair

Not implemented in PostgreSQL.

stat

Not implemented in PostgreSQL.

structure

This returns, in list context, an hash and, in scalar context, an hash reference of the table structure.

The hash, or hash reference returned contains the column name and its definition.

The data returned is cached, so it fetches the information from PostgreSQL only once.

This method will also set the following object properties:

  • "type" in DB::Object::Tables

    The table type.

  • "schema" in DB::Object::Tables

    The table schema.

  • default

    A column name to default value hash reference

  • fields

    A column name to field position (integer) hash reference

  • null

    A column name to a boolean representing whether the column is nullable or not.

  • "primary" in DB::Object::Tables

    An array reference of column names that are used as primary key for the table.

  • structure

    A column name to its sql definition

  • types

    A column name to column data type hash reference

table_info

This is an alias for "table_info" in DB::Object::Postgres

unlock

This will unlock a previously locked table.

If an argument is provided, this calls instead CORE::unlock passing it whatever parameters provided.

Otherwise, it will prepare a query UNLOCK TABLES and returns the statement handler.

If it is called in void context, this will execute the statement handler immediately.

SEE ALSO

perl

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

Copyright (c) 2019-2021 DEGUEST Pte. Ltd.

You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.