NAME

DB::Object::Postgres::Query - Query Object for PostgreSQL

SYNOPSIS

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

VERSION

    v0.1.8

DESCRIPTION

This is a Postgres specific query object.

METHODS

binded_having

Sets or gets the array object (Module::Generic::Array) for the binded value in HAVING clauses.

binded_types_as_param

Returns an array object (Module::Generic::Array) of binded params types.

dollar_placeholder

Provided with a true value, and this will set the placeholder to be a dollar, such as $1, $2, etc for this query only.

It returns the current boolean value.

format_from_epoch

This takes the parameters bind and value and returns a formatted TO_TIMESTAMP expression.

format_statement

This method is called to format select, delete and insert query.

It takes the following parameters

data

order

table

It uses the parameters passed to "select" in DB::Object::Query, "delete" in DB::Object::Query and "insert" in DB::Object::Query and format them properly.

If no arguments were passed to those query methods, it will use a default sorted columns instead.

In list context, this returns the fields and values formatted as string, and in scalar context it returns the fields formatted.

format_to_epoch

This takes the parameters bind, value and quote and returns a formatted expression to returns the epoch value out of the given field.

having

Calls "_where_having" in DB::Object::Query to build a having clause.

limit

Build a new DB::Object::Query::Clause clause object by calling "_process_limit" and return it.

on_conflict

Provided with some options and this will build a ON CONFLICT clause (DB::Object::Query::Clause). This is only available for PostgreSQL version 9.5 or above.

action

Valid value can be nothing and in which case, nothing will be done by the database upon conflict.

    INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
        ON CONFLICT (did) DO NOTHING;

or

    INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
        ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

Value can also be ignore instructing the database to simply ignore conflict.

If the value is update, then this will set a callback routine to format an update statement using "format_update" in DB::Object::Query

If the original insert or update uses placeholders, then the DO UPDATE will also use the same placeholders and the DB::Object::Statement object will act accordingly when being provided the binded values. That is, it will double them to allocate those binded value also for the DO UPDATE part of the query.

The callback will be called by "insert" in DB::Object::Query or "update" in DB::Object::Query, because the "on_conflict" relies on query columns being previously set.

fields

An array (or array object) of fields to use with action set to update

    $q->on_conflict({
        target  => 'name',
        action  => 'update,
        fields  => [qw( first_name last_name )],
    });

This will turn the DO UPDATE prepending each field with the special keyword EXCLUDED

    INSERT INTO distributors (did, dname)
        VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
        ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

target

Target can be a table column.

    $q->on_conflict({
        target  => 'name',
        action  => 'ignore',
    });

or it can also be a constraint name:

    $q->on_conflict({
        target  => 'on constraint my_table_idx_name',
        action  => 'ignore',
    });

Value for target can also be a scalar reference and it will be used as-is

    $q->on_conflict({
        target  => \'on constraint my_table_idx_name',
        action  => 'ignore',
    });

Value for target can also be an array or array object (like Module::Generic::Array) and the array will be joined using a comma.

If no target argument was provided, then action must be set to nothing or this will return an error.

where

You can also provide a WHERE expression in the conflict and it will be added literally.

    $q->on_conflict({
        target  => 'did',
        action  => 'ignore',
        where   => 'is_active',
    });

    INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
        ON CONFLICT (did) WHERE is_active DO NOTHING;

See PostgreSQL documentation for more information.

reset

If the object property query_reset is not already set, this will remove the following properties from the current query object, set "enhance" in DB::Object::Query to true and return the query object.

Properties removed are: alias local binded binded_values binded_where binded_limit binded_group binded_having binded_order where limit group_by on_conflict _on_conflict order_by reverse from_unixtime unix_timestamp sorted

reset_bind

Reset all the following object properties to an anonymous array: binded binded_where binded_group binded_having binded_order binded_limit

returning

This feature is available with PostgreSQL version 8.2 or above, otherwise an error is returned.

It expects a string that is used to build the RETURNING clause.

    # will instruct the database to return all the table columns
    $q->returning( '*' );

or

    $q->returning( 'id' );

But don't pass a reference:

    $q->returning( [qw( id name age )] );

It returns a new DB::Object::Postgres::Query::Clause object.

See PostgreSQL documentation for more information

server_prepare

Sets or gets the boolean value for whether you want the sql statement to be prepared server-side or not.

Please see the warnings about this breaking change implemented since version 9.40 "prepare" in DBD::Pg.

Since PostgreSQL does not see the parameters that are passed at statement execution, it is possible it misinterpret. Consider this:

    my $ip = '192.168.2.12';
    my $ip_tbl = $dbh->ip_registry;
    # Check if the ip match an ip block
    my $P = $dbh->placeholder( type => 'inet' );
    $ip_tbl->where( $dbh->OR( $ip_tbl->fo->ip_addr == "INET $P", "INET $P" << $ip_tbl->fo->ip_addr ) );
    $sth = $ip_tbl->select || die( "An error occurred while trying to format query to check if ip is in the registry." );
    $sth->exec( $ip, $ip ) || die( "An error occurred while trying to execute query to check if ip is in the registry: ", $sth->error );

This would yield the server error: syntax error at or near "$1"

The solution would be to de-activate server prepare for this query only:

    $ip_tbl->query_object->server_prepare(0);

_query_components

This is called by the various query methods like "select" in DB::Object::Query, "insert" in DB::Object::Query, "update" in DB::Object::Query, "delete" in DB::Object::Query

It will get the various query components (group, having, sort, order, limit) that have been set and add them formatted to an array that is returned.

This version of "_query_components" exists here to provide PostgreSQL specific implementation. See also the generic one in "_query_components" in DB::Object::Query

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