NAME

DB::Object::Postgres::Statement - PostgreSQL Statement Object

SYNOPSIS

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

VERSION

    v0.301.2

DESCRIPTION

This is a PostgreSQL specific statement object class.

METHODS

disable_trigger

Provided some hash or hash reference of options and this will modify the current query to temporarily disable trigger and return a new statement handler object.

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

For example, let's say you have a table properties and you do not want properties to be removed, but instead marked as deleted and to achieve that you create a table trigger that is triggered before the delete query is executed and does instead an update setting the property status to deleted.

    CREATE TABLE properties (
         id     SERIAL NOT NULL
        ,name   VARCHAR(255) NOT NULL
        ,status VARCHAR(12) NOT NULL DEFAULT 'active'
        ,CONSTRAINT pk_properties PRIMARY KEY(id)
        ,CONSTRAINT idx_properties UNIQUE(name)
    );

    CREATE OR REPLACE FUNCTION f_properties_table() RETURNS TRIGGER AS $$
        BEGIN
            UPDATE properties SET status = 'deleted' WHERE name = OLD.name;
            -- To prevent the original query from being executed
            RETURN NULL;
        END;
    $$ LANGUAGE 'plpgsql';

    DROP TRIGGER IF EXISTS t_properties_table ON properties;
    CREATE TRIGGER t_properties_table
    BEFORE DELETE ON properties
    FOR EACH ROW EXECUTE PROCEDURE f_properties_table();

If you issued a query like:

    $tbl->insert( name => 'max_connections', status => 'active' );

And then, to remove it:

    $tbl->where( $tbl->fo->name == 'max_connections' );
    $tbl->delete;

The trigger will prevent that property from being removed and instead the row's status will be changed to deleted, but if you really wanted to force remove that property, you would do:

    $tbl->where( $tbl->fo->name == 'max_connections' );
    $tbl->delete->disable_trigger;

And this would execute the following query:

    ALTER TABLE properties DISABLE TRIGGER USER; DELETE FROM properties WHERE name = 'max_connections'; ALTER TABLE properties ENABLE TRIGGER USER;

distinct

Provided with an optional column and called upon a SELECT query and this will modify the query to add the keyword DISTINCT

    $sth->distinct;
    # produces SELECT DISTINCT....
    $sth->distinct( 'name' );
    # produces SELECT DISTINCT ON (name)....

dump

This will dump the result of the query to STDOUT or to a file if file argument is provided, or if a filehandle is provided with fh, it will be used to print out the data.

It takes also a vsep, which defaults to a command and a hsep which defaults to a new line.

It returns the current object.

ignore

This returns an error as INSERT | UPDATE | ALTER IGNORE is not supported by PostgreSQL.

last_insert_id

Will call "last_insert_id" in DB::Object with the necessary parameters to get the last inserted table id.

name

Sets or gets the name of the prepared statement. The name set will show up in the PostgreSQL server log.

See "prepare" in DBD::Pg

only

Upon being called, this will add the keyword ONLY to the query.

If the original query type is SELECT, this will become something like:

    SELECT some_columns, other FROM ONLY some_table

For DELETE query it would be like:

    DELETE FROM ONLY some_table

For UPDATE query, it would look like:

    UPDATE ONLY some_table 

It returns the current statement object.

See PostgreSQL documentation for more information

priority

This is unsupported under PostgreSQL and if used returns an error.

wait

This is unsupported under PostgreSQL and it will be silently ignore, returning the current object.

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.

cpanm DB::Object

perl -MCPAN -e shell
install DB::Object