MAN page from OpenSuSE perl-Mojo-Pg-4.13-2.1.noarch.rpm

SQL::Abstract::Pg

Section: User Contributed Perl Documentation (3)
Updated: 2019-01-20
Index

NAME

SQL::Abstract::Pg - PostgreSQL

SYNOPSIS

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

DESCRIPTION

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

JSON

In many places (as supported by SQL::Abstract) you can use the "-json" unaryop 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]}}});

INSERT

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

ON CONFLICT

The "on_conflict" option can be used to generate "INSERT" queries with"ON CONFLICT" clauses. So far "undef" to pass "DO NOTHING", array referencesto pass "DO UPDATE" with conflict targets and a "SET" expression, scalarreferences to pass literal SQL and array reference references to pass literalSQL 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']});

SELECT

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

AS

The $fields argument now also accepts array references containing arrayreferences with field names and aliases, as well as array references containingscalar references to pass literal SQL and array reference references to passliteral 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']);

JOIN

The $source argument now also accepts array references containing not onlytable names, but also array references with tables to generate "JOIN" clausesfor.

  # "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']]);

ORDER BY

Alternatively to the $order argument accepted by SQL::Abstract you can nowalso 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'}});

LIMIT/OFFSET

The "limit" and "offset" options can be used to generate "SELECT" querieswith "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});

GROUP BY

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 scalarreferences 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'});

HAVING

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'}});

FOR

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 topass 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'});

METHODS

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

Index

NAME

SYNOPSIS

DESCRIPTION

JSON

INSERT

ON CONFLICT

SELECT

AS
JOIN
ORDER BY
LIMIT/OFFSET
GROUP BY
HAVING
FOR

METHODS

SEE ALSO

This document was created byman2html,using the manual pages.