1SQL::Abstract::Pg(3) User Contributed Perl Documentation SQL::Abstract::Pg(3)
2
6 SQL::Abstract::Pg - PostgreSQL
7
9 use SQL::Abstract::Pg;
10 my $abstract = SQL::Abstract::Pg->new;
12 say $abstract->select('some_table');
13
15 SQL::Abstract::Pg extends SQL::Abstract with a few PostgreSQL features
16 used by Mojo::Pg.
17 JSON
19 In many places (as supported by SQL::Abstract) you can use the "-json"
20 unary op to encode JSON from Perl data structures.
21 # "update some_table set foo = '[1,2,3]' where bar = 23"
23 $abstract->update('some_table', {foo => {-json => [1, 2, 3]}}, {bar => 23});
24 # "select * from some_table where foo = '[1,2,3]'"
26 $abstract->select('some_table', '*', {foo => {'=' => {-json => [1, 2, 3]}}});
27
29 $abstract->insert($table, \@values || \%fieldvals, \%options);
30 ON CONFLICT
32 The "on_conflict" option can be used to generate "INSERT" queries with
33 "ON CONFLICT" clauses. So far, "undef" to pass "DO NOTHING", array
34 references to pass "DO UPDATE" with conflict targets and a "SET"
35 expression, scalar references to pass literal SQL and array reference
36 references to pass literal SQL with bind values are supported.
37 # "insert into t (a) values ('b') on conflict do nothing"
39 $abstract->insert('t', {a => 'b'}, {on_conflict => undef});
40 # "insert into t (a) values ('b') on conflict do nothing"
42 $abstract->insert('t', {a => 'b'}, {on_conflict => \'do nothing'});
43 This includes operations commonly referred to as "upsert".
45 # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
47 $abstract->insert('t', {a => 'b'}, {on_conflict => [a => {a => 'c'}]});
48 # "insert into t (a, b) values ('c', 'd')
50 # on conflict (a, b) do update set a = 'e'"
51 $abstract->insert(
52 't', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});
53 # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
55 $abstract->insert(
56 't', {a => 'b'}, {on_conflict => \['(a) do update set a = ?', 'c']});
57
59 $abstract->select($source, $fields, $where, $order);
60 $abstract->select($source, $fields, $where, \%options);
61 AS
63 The $fields argument now also accepts array references containing array
64 references with field names and aliases, as well as array references
65 containing scalar references to pass literal SQL and array reference
66 references to pass literal SQL with bind values.
67 # "select foo as bar from some_table"
69 $abstract->select('some_table', [[foo => 'bar']]);
70 # "select foo, bar as baz, yada from some_table"
72 $abstract->select('some_table', ['foo', [bar => 'baz'], 'yada']);
73 # "select extract(epoch from foo) as foo, bar from some_table"
75 $abstract->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);
76 # "select 'test' as foo, bar from some_table"
78 $abstract->select('some_table', [\['? as foo', 'test'], 'bar']);
79 JOIN
81 The $source argument now also accepts array references containing not
82 only table names, but also array references with tables to generate
83 "JOIN" clauses for.
84 # "select * from foo join bar on (bar.foo_id = foo.id)"
86 $abstract->select(['foo', ['bar', foo_id => 'id']]);
87 # "select * from foo join bar on (foo.id = bar.foo_id)"
89 $abstract->select(['foo', ['bar', 'foo.id' => 'bar.foo_id']]);
90 # "select * from a join b on (b.a_id = a.id) join c on (c.a_id = a.id)"
92 $abstract->select(['a', ['b', a_id => 'id'], ['c', a_id => 'id']]);
93 # "select * from foo left join bar on (bar.foo_id = foo.id)"
95 $abstract->select(['foo', [-left => 'bar', foo_id => 'id']]);
96 # "select * from a left join b on (b.a_id = a.id and b.a_id2 = a.id2)"
98 $abstract->select(['a', [-left => 'b', a_id => 'id', a_id2 => 'id2']]);
99 ORDER BY
101 Alternatively to the $order argument accepted by SQL::Abstract you can
102 now also pass a hash reference with various options. This includes
103 "order_by", which takes the same values as the $order argument.
104 # "select * from some_table order by foo desc"
106 $abstract->select('some_table', '*', undef, {order_by => {-desc => 'foo'}});
107 LIMIT/OFFSET
109 The "limit" and "offset" options can be used to generate "SELECT"
110 queries with "LIMIT" and "OFFSET" clauses.
111 # "select * from some_table limit 10"
113 $abstract->select('some_table', '*', undef, {limit => 10});
114 # "select * from some_table offset 5"
116 $abstract->select('some_table', '*', undef, {offset => 5});
117 # "select * from some_table limit 10 offset 5"
119 $abstract->select('some_table', '*', undef, {limit => 10, offset => 5});
120 GROUP BY
122 The "group_by" option can be used to generate "SELECT" queries with
123 "GROUP BY" clauses. So far, array references to pass a list of fields
124 and scalar references to pass literal SQL are supported.
125 # "select * from some_table group by foo, bar"
127 $abstract->select('some_table', '*', undef, {group_by => ['foo', 'bar']});
128 # "select * from some_table group by foo, bar"
130 $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});
131 HAVING
133 The "having" option can be used to generate "SELECT" queries with
134 "HAVING" clauses, which takes the same values as the $where argument.
135 # "select * from t group by a having b = 'c'"
137 $abstract->select('t', '*', undef, {group_by => ['a'], having => {b => 'c'}});
138 FOR
140 The "for" option can be used to generate "SELECT" queries with "FOR"
141 clauses. So far, the scalar value "update" to pass "UPDATE" and scalar
142 references to pass literal SQL are supported.
143 # "select * from some_table for update"
145 $abstract->select('some_table', '*', undef, {for => 'update'});
146 # "select * from some_table for update skip locked"
148 $abstract->select('some_table', '*', undef, {for => \'update skip locked'});
149
151 SQL::Abstract::Pg inherits all methods from SQL::Abstract.
152
154 Mojo::Pg, Mojolicious::Guides, <https://mojolicious.org>.
155perl v5.32.0 2020-07-28 SQL::Abstract::Pg(3)