DB::Object::Postgres::Statement - PostgreSQL Statement Object
use DB::Object::Postgres::Statement; my $this = DB::Object::Postgres::Statement->new || die( DB::Object::Postgres::Statement->error, "\n" );
v0.301.2
This is a PostgreSQL specific statement object class.
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.
properties
deleted
delete
update
status
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;
Provided with an optional column and called upon a SELECT query and this will modify the query to add the keyword DISTINCT
SELECT
DISTINCT
$sth->distinct; # produces SELECT DISTINCT.... $sth->distinct( 'name' ); # produces SELECT DISTINCT ON (name)....
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.
This returns an error as INSERT | UPDATE | ALTER IGNORE is not supported by PostgreSQL.
INSERT
UPDATE
ALTER IGNORE
Will call "last_insert_id" in DB::Object with the necessary parameters to get the last inserted table id.
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
Upon being called, this will add the keyword ONLY to the query.
ONLY
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
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
This is unsupported under PostgreSQL and if used returns an error.
This is unsupported under PostgreSQL and it will be silently ignore, returning the current object.
perl
Jacques Deguest <jack@deguest.jp>
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.
To install DB::Object, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DB::Object
CPAN shell
perl -MCPAN -e shell install DB::Object
For more information on module installation, please visit the detailed CPAN module installation guide.