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
SQL::Abstract::Pg(3) User Contributed Perl Documentation SQL::Abstract::Pg(3)

SQL::Abstract::Pg - PostgreSQL features for SQL::Abstract

  use SQL::Abstract::Pg;

  my $abstract = SQL::Abstract::Pg->new;
  say $abstract->select('some_table');

SQL::Abstract::Pg extends SQL::Abstract with a few PostgreSQL features used by Mojo::Pg.

In many places (as supported by SQL::Abstract) you can use the "-json" unary op to encode JSON from Perl data structures.

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

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

  $abstract->insert($table, \@values || \%fieldvals, \%options);

The "on_conflict" option can be used to generate "INSERT" queries with "ON CONFLICT" clauses. So far, "undef" to pass "DO NOTHING", array references to pass "DO UPDATE" with conflict targets and a "SET" expression, scalar references to pass literal SQL and array reference references to pass literal SQL with bind values are supported.

  # "INSERT INTO t (a) VALUES ('b') ON CONFLICT DO NOTHING"
  $abstract->insert('t', {a => 'b'}, {on_conflict => undef});

  # "INSERT INTO t (a) VALUES ('b') ON CONFLICT DO NOTHING"
  $abstract->insert('t', {a => 'b'}, {on_conflict => \'do nothing'});

This includes operations commonly referred to as "upsert".

  # "INSERT INTO t (a) VALUES ('b') ON CONFLICT (a) DO UPDATE SET a = 'c'"
  $abstract->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'"
  $abstract->insert('t', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});

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

  $abstract->select($source, $fields, $where, $order);
  $abstract->select($source, $fields, $where, \%options);

The $fields argument now also accepts array references containing array references with field names and aliases, as well as array references containing scalar references to pass literal SQL and array reference references to pass literal SQL with bind values.

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

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

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

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

The $source argument now also accepts array references containing not only table names, but also array references with tables to generate "JOIN" clauses for.

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

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

  # "SELECT * FROM a JOIN b ON (b.a_id = a.id) JOIN c ON (c.a_id = a.id)"
  $abstract->select(['a', ['b', a_id => 'id'], ['c', a_id => 'id']]);

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

  # "SELECT * FROM a LEFT JOIN b ON (b.a_id = a.id AND b.a_id2 = a.id2)"
  $abstract->select(['a', [-left => 'b', a_id => 'id', a_id2 => 'id2']]);

Alternatively to the $order argument accepted by SQL::Abstract you can now also pass a hash reference with various options. This includes "order_by", which takes the same values as the $order argument.

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

The "limit" and "offset" options can be used to generate "SELECT" queries with "LIMIT" and "OFFSET" clauses.

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

  # "SELECT * FROM some_table OFFSET 5"
  $abstract->select('some_table', '*', undef, {offset => 5});

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

The "group_by" option can be used to generate "SELECT" queries with "GROUP BY" clauses. So far, array references to pass a list of fields and scalar references to pass literal SQL are supported.

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

  # "SELECT * FROM some_table GROUP BY foo, bar"
  $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});

The "having" option can be used to generate "SELECT" queries with "HAVING" clauses, which takes the same values as the $where argument.

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

The "for" option can be used to generate "SELECT" queries with "FOR" clauses. So far, the scalar value "update" to pass "UPDATE" and scalar references to pass literal SQL are supported.

  # "SELECT * FROM some_table FOR UPDATE"
  $abstract->select('some_table', '*', undef, {for => 'update'});

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

SQL::Abstract::Pg inherits all methods from SQL::Abstract.

Sebastian Riedel, "sri@cpan.org".

Copyright (C) 2014-2021, Sebastian Riedel and others.

This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0.

<https://github.com/mojolicious/sql-abstract-pg>, Mojolicious::Guides, <https://mojolicious.org>.
2021-02-20 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.