GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages
Mojo::Pg::Database(3) User Contributed Perl Documentation Mojo::Pg::Database(3)

Mojo::Pg::Database - Database

  use Mojo::Pg::Database;

  my $db = Mojo::Pg::Database->new(pg => $pg, dbh => $dbh);
  $db->query('SELECT * FROM foo') ->hashes->map(sub { $_->{bar} })->join("\n")->say;

Mojo::Pg::Database is a container for DBD::Pg database handles used by Mojo::Pg.

Mojo::Pg::Database inherits all events from Mojo::EventEmitter and can emit the following new ones.

  $db->on(close => sub ($db) {
    ...
  });

Emitted when the database connection gets closed while waiting for notifications.

  $db->on(notification => sub ($db, $name, $pid, $payload) {
    ...
  });

Emitted when a notification has been received.

Mojo::Pg::Database implements the following attributes.

  my $dbh = $db->dbh;
  $db     = $db->dbh($dbh);

DBD::Pg database handle used for all queries.

  # Use DBI utility methods
  my $quoted = $db->dbh->quote_identifier('foo.bar');

  my $pg = $db->pg;
  $db    = $db->pg(Mojo::Pg->new);

Mojo::Pg object this database belongs to. Note that this attribute is weakened.

  my $class = $db->results_class;
  $db       = $db->results_class('MyApp::Results');

Class to be used by "query", defaults to Mojo::Pg::Results. Note that this class needs to have already been loaded before "query" is called.

Mojo::Pg::Database inherits all methods from Mojo::EventEmitter and implements the following new ones.

  my $tx = $db->begin;

Begin transaction and return Mojo::Pg::Transaction object, which will automatically roll back the transaction unless "commit" in Mojo::Pg::Transaction has been called before it is destroyed.

  # Insert rows in a transaction
  eval {
    my $tx = $db->begin;
    $db->insert('frameworks', {name => 'Catalyst'});
    $db->insert('frameworks', {name => 'Mojolicious'});
    $tx->commit;
  };
  say $@ if $@;

  my $results = $db->delete($table, \%where, \%options);

Generate a "DELETE" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and execute it with "query". You can also append a callback to perform operations non-blocking.

  $db->delete(some_table => sub ($db, $err, $results) {
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Use all the same argument variations you would pass to the "delete" method of SQL::Abstract.

  # "DELETE FROM some_table"
  $db->delete('some_table');

  # "DELETE FROM some_table WHERE foo = 'bar'"
  $db->delete('some_table', {foo => 'bar'});

  # "DELETE from some_table WHERE foo LIKE '%test%'"
  $db->delete('some_table', {foo => {-like => '%test%'}});

  # "DELETE FROM some_table WHERE foo = 'bar' RETURNING id"
  $db->delete('some_table', {foo => 'bar'}, {returning => 'id'});

  my $promise = $db->delete_p($table, \%where, \%options);

Same as "delete", but performs all operations non-blocking and returns a Mojo::Promise object instead of accepting a callback.

  $db->delete_p('some_table')->then(sub ($results) {
    ...
  })->catch(sub ($err) {
    ...
  })->wait;

  $db->disconnect;

Disconnect "dbh" and prevent it from getting reused.

  $db = $db->dollar_only;

Activate "pg_placeholder_dollaronly" for next "query" call and allow "?" to be used as an operator.

  # Check for a key in a JSON document
  $db->dollar_only->query('SELECT * FROM foo WHERE bar ? $1', 'baz')
    ->expand->hashes->map(sub { $_->{bar}{baz} })->join("\n")->say;

  my $results = $db->insert($table, \@values || \%fieldvals, \%options);

Generate an "INSERT" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and execute it with "query". You can also append a callback to perform operations non-blocking.

  $db->insert(some_table => {foo => 'bar'} => sub ($db, $err, $results) {
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Use all the same argument variations you would pass to the "insert" method of SQL::Abstract.

  # "INSERT INTO some_table (foo, baz) VALUES ('bar', 'yada')"
  $db->insert('some_table', {foo => 'bar', baz => 'yada'});

  # "INSERT INTO some_table (foo) VALUES ({1,2,3})"
  $db->insert('some_table', {foo => [1, 2, 3]});

  # "INSERT INTO some_table (foo) VALUES ('bar') RETURNING id"
  $db->insert('some_table', {foo => 'bar'}, {returning => 'id'});

  # "INSERT INTO some_table (foo) VALUES ('bar') RETURNING id, foo"
  $db->insert('some_table', {foo => 'bar'}, {returning => ['id', 'foo']});

As well as some PostgreSQL specific extensions added by SQL::Abstract::Pg.

  # "INSERT INTO some_table (foo) VALUES ('{"test":23}')"
  $db->insert('some_table', {foo => {-json => {test => 23}}});

  # "INSERT INTO some_table (foo) VALUES ('bar') ON CONFLICT DO NOTHING"
  $db->insert('some_table', {foo => 'bar'}, {on_conflict => undef});

Including operations commonly referred to as "upsert".

  # "INSERT INTO t (a) VALUES ('b') ON CONFLICT (a) DO UPDATE SET a = 'c'"
  $db->insert('t', {a => 'b'}, {on_conflict => [a => {a => 'c'}]});

  # "INSERT INTO t (a, b) VALUES ('c', 'd') ON CONFLICT (a, b) DO UPDATE SET a = 'e'"
  $db->insert('t', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});

  my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);

Same as "insert", but performs all operations non-blocking and returns a Mojo::Promise object instead of accepting a callback.

  $db->insert_p(some_table => {foo => 'bar'})->then(sub ($results) {
    ...
  })->catch(sub ($err) {
    ...
  })->wait;

  my $bool = $db->is_listening;

Check if "dbh" is listening for notifications.

  $db = $db->listen('foo');

Subscribe to a channel and receive "notification" events when the Mojo::IOLoop event loop is running.

  $db = $db->notify('foo');
  $db = $db->notify(foo => 'bar');

Notify a channel.

  my $pid = $db->pid;

Return the process id of the backend server process.

  my $bool = $db->ping;

Check database connection.

  my $results = $db->query('SELECT * FROM foo');
  my $results = $db->query('INSERT INTO foo VALUES (?, ?, ?)', @values);
  my $results = $db->query('SELECT ?::JSON AS foo', {-json => {bar => 'baz'}});

Execute a blocking SQL <http://www.postgresql.org/docs/current/static/sql.html> statement and return a results object based on "results_class" (which is usually Mojo::Pg::Results) with the query results. The DBD::Pg statement handle will be automatically reused when it is not active anymore, to increase the performance of future queries. You can also append a callback to perform operations non-blocking.

  $db->query('INSERT INTO foo VALUES (?, ?, ?)' => @values => sub ($db, $err, $results) {
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Hash reference arguments containing a value named "-json" or "json" will be encoded to JSON text with "to_json" in Mojo::JSON. To accomplish the reverse, you can use the method "expand" in Mojo::Pg::Results, which automatically decodes all fields of the types "json" and "jsonb" with "from_json" in Mojo::JSON to Perl values.

  # "I ♥ Mojolicious!"
  $db->query('SELECT ?::JSONB AS foo', {-json => {bar => 'I ♥ Mojolicious!'}}) ->expand->hash->{foo}{bar};

Hash reference arguments containing values named "type" and "value" can be used to bind specific DBD::Pg data types to placeholders.

  # Insert binary data
  use DBD::Pg ':pg_types';
  $db->query('INSERT INTO bar VALUES (?)', {type => PG_BYTEA, value => $bytes});

  my $promise = $db->query_p('SELECT * FROM foo');

Same as "query", but performs all operations non-blocking and returns a Mojo::Promise object instead of accepting a callback.

  $db->query_p('INSERT INTO foo VALUES (?, ?, ?)' => @values)->then(sub ($results) {
    ...
  })->catch(sub ($err) {
    ...
  })->wait;

  my $results = $db->select($source, $fields, $where, \%options);

Generate a "SELECT" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and execute it with "query". You can also append a callback to perform operations non-blocking.

  $db->select(some_table => ['foo'] => {bar => 'yada'} => sub ($db, $err, $results) {
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Use all the same argument variations you would pass to the "select" method of SQL::Abstract.

  # "SELECT * FROM some_table"
  $db->select('some_table');

  # "SELECT id, foo FROM some_table"
  $db->select('some_table', ['id', 'foo']);

  # "SELECT * FROM some_table WHERE foo = 'bar'"
  $db->select('some_table', undef, {foo => 'bar'});

  # "SELECT * FROM some_table WHERE foo LIKE '%test%'"
  $db->select('some_table', undef, {foo => {-like => '%test%'}});

As well as some PostgreSQL specific extensions added by SQL::Abstract::Pg.

  # "SELECT * FROM foo JOIN bar ON (bar.foo_id = foo.id)"
  $db->select(['foo', ['bar', foo_id => 'id']]);

  # "SELECT * FROM foo LEFT JOIN bar ON (bar.foo_id = foo.id)"
  $db->select(['foo', [-left => 'bar', foo_id => 'id']]);

  # "SELECT foo AS bar FROM some_table"
  $db->select('some_table', [[foo => 'bar']]);

  # "SELECT * FROM some_table WHERE foo = '[1,2,3]'"
  $db->select('some_table', '*', {foo => {'=' => {-json => [1, 2, 3]}}});

  # "SELECT EXTRACT(EPOCH FROM foo) AS foo, bar FROM some_table"
  $db->select('some_table', [\'extract(epoch from foo) AS foo', 'bar']);

  # "SELECT 'test' AS foo, bar FROM some_table"
  $db->select('some_table', [\['? AS foo', 'test'], 'bar']);

Including a new last argument to pass many new options.

  # "SELECT * FROM some_table WHERE foo = 'bar' ORDER BY id DESC"
  $db->select('some_table', '*', {foo => 'bar'}, {order_by => {-desc => 'id'}});

  # "SELECT * FROM some_table LIMIT 10 OFFSET 20"
  $db->select('some_table', '*', undef, {limit => 10, offset => 20});

  # "SELECT * FROM some_table WHERE foo = 23 GROUP BY foo, bar"
  $db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});

  # "SELECT * FROM t WHERE a = 'b' GROUP BY c HAVING d = 'e'"
  $db->select('t', '*', {a => 'b'}, {group_by => ['c'], having => {d => 'e'}});

  # "SELECT * FROM some_table WHERE id = 1 FOR UPDATE"
  $db->select('some_table', '*', {id => 1}, {for => 'update'});

  # "SELECT * FROM some_table WHERE id = 1 FOR UPDATE SKIP LOCKED"
  $db->select('some_table', '*', {id => 1}, {for => \'update skip locked'});

  my $promise = $db->select_p($source, $fields, $where, \%options);

Same as "select", but performs all operations non-blocking and returns a Mojo::Promise object instead of accepting a callback.

  $db->select_p(some_table => ['foo'] => {bar => 'yada'})->then(sub ($results) {
    ...
  })->catch(sub ($err) {
    ...
  })->wait;

  my $tables = $db->tables;

Return table and view names for this database, that are visible to the current user and not internal, as an array reference.

  # Names of all tables
  say for @{$db->tables};

  $db = $db->unlisten('foo');
  $db = $db->unlisten('*');

Unsubscribe from a channel, "*" can be used to unsubscribe from all channels.

  my $results = $db->update($table, \%fieldvals, \%where, \%options);

Generate an "UPDATE" statement with "abstract" in Mojo::Pg (usually an SQL::Abstract::Pg object) and execute it with "query". You can also append a callback to perform operations non-blocking.

  $db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub ($db, $err, $results) {
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

Use all the same argument variations you would pass to the "update" method of SQL::Abstract.

  # "UPDATE some_table SET foo = 'bar' WHERE id = 23"
  $db->update('some_table', {foo => 'bar'}, {id => 23});

  # "UPDATE some_table SET foo = {1,2,3} WHERE id = 23"
  $db->update('some_table', {foo => [1, 2, 3]}, {id => 23});

  # "UPDATE some_table SET foo = 'bar' WHERE foo LIKE '%test%'"
  $db->update('some_table', {foo => 'bar'}, {foo => {-like => '%test%'}});

  # "UPDATE some_table SET foo = 'bar' WHERE id = 23 RETURNING id"
  $db->update('some_table', {foo => 'bar'}, {id => 23}, {returning => 'id'});

  # "UPDATE some_table SET foo = '[1,2,3]' WHERE bar = 23"
  $db->update('some_table', {foo => {-json => [1, 2, 3]}}, {bar => 23});

  my $promise = $db->update_p($table, \%fieldvals, \%where, \%options);

Same as "update", but performs all operations non-blocking and returns a Mojo::Promise object instead of accepting a callback.

  $db->update_p(some_table => {foo => 'baz'} => {foo => 'bar'})->then(sub ($results) {
    ...
  })->catch(sub ($err) {
    ...
  })->wait;

Mojo::Pg, Mojolicious::Guides, <https://mojolicious.org>.
2022-03-10 perl v5.32.1

Search for    or go to Top of page |  Section 3 |  Main Index

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with ManDoc.