SQL::Abstract::Pg(3pm)

1SQL::Abstract::Pg(3)  User Contributed Perl Documentation SQL::Abstract::Pg(3)
2
3
4

NAME

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

SYNOPSIS

9         use SQL::Abstract::Pg;
10
11         my $abstract = SQL::Abstract::Pg->new;
12         say $abstract->select('some_table');
13

DESCRIPTION

15       SQL::Abstract::Pg extends SQL::Abstract with a few PostgreSQL features
16       used by Mojo::Pg.
17
18   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
22         # "UPDATE some_table SET foo = '[1,2,3]' WHERE bar = 23"
23         $abstract->update('some_table', {foo => {-json => [1, 2, 3]}}, {bar => 23});
24
25         # "SELECT * FROM some_table WHERE foo = '[1,2,3]'"
26         $abstract->select('some_table', '*', {foo => {'=' => {-json => [1, 2, 3]}}});
27

INSERT

29         $abstract->insert($table, \@values || \%fieldvals, \%options);
30
31   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
38         # "INSERT INTO t (a) VALUES ('b') ON CONFLICT DO NOTHING"
39         $abstract->insert('t', {a => 'b'}, {on_conflict => undef});
40
41         # "INSERT INTO t (a) VALUES ('b') ON CONFLICT DO NOTHING"
42         $abstract->insert('t', {a => 'b'}, {on_conflict => \'do nothing'});
43
44       This includes operations commonly referred to as "upsert".
45
46         # "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
49         # "INSERT INTO t (a, b) VALUES ('c', 'd') ON CONFLICT (a, b) DO UPDATE SET a = 'e'"
50         $abstract->insert('t', {a => 'c', b => 'd'}, {on_conflict => [['a', 'b'] => {a => 'e'}]});
51
52         # "INSERT INTO t (a) VALUES ('b') ON CONFLICT (a) DO UPDATE SET a = 'c'"
53         $abstract->insert('t', {a => 'b'}, {on_conflict => \['(a) do update set a = ?', 'c']});
54

SELECT

56         $abstract->select($source, $fields, $where, $order);
57         $abstract->select($source, $fields, $where, \%options);
58
59   AS
60       The $fields argument now also accepts array references containing array
61       references with field names and aliases, as well as array references
62       containing scalar references to pass literal SQL and array reference
63       references to pass literal SQL with bind values.
64
65         # "SELECT foo AS bar FROM some_table"
66         $abstract->select('some_table', [[foo => 'bar']]);
67
68         # "SELECT foo, bar AS baz, yada FROM some_table"
69         $abstract->select('some_table', ['foo', [bar => 'baz'], 'yada']);
70
71         # "SELECT EXTRACT(EPOCH FROM foo) AS foo, bar FROM some_table"
72         $abstract->select('some_table', [\'extract(epoch from foo) AS foo', 'bar']);
73
74         # "SELECT 'test' AS foo, bar FROM some_table"
75         $abstract->select('some_table', [\['? AS foo', 'test'], 'bar']);
76
77   JOIN
78       The $source argument now also accepts array references containing not
79       only table names, but also array references with tables to generate
80       "JOIN" clauses for.
81
82         # "SELECT * FROM foo JOIN bar ON (bar.foo_id = foo.id)"
83         $abstract->select(['foo', ['bar', foo_id => 'id']]);
84
85         # "SELECT * FROM foo JOIN bar ON (foo.id = bar.foo_id)"
86         $abstract->select(['foo', ['bar', 'foo.id' => 'bar.foo_id']]);
87
88         # "SELECT * FROM a JOIN b ON (b.a_id = a.id) JOIN c ON (c.a_id = a.id)"
89         $abstract->select(['a', ['b', a_id => 'id'], ['c', a_id => 'id']]);
90
91         # "SELECT * FROM foo LEFT JOIN bar ON (bar.foo_id = foo.id)"
92         $abstract->select(['foo', [-left => 'bar', foo_id => 'id']]);
93
94         # "SELECT * FROM a LEFT JOIN b ON (b.a_id = a.id AND b.a_id2 = a.id2)"
95         $abstract->select(['a', [-left => 'b', a_id => 'id', a_id2 => 'id2']]);
96
97   ORDER BY
98       Alternatively to the $order argument accepted by SQL::Abstract you can
99       now also pass a hash reference with various options. This includes
100       "order_by", which takes the same values as the $order argument.
101
102         # "SELECT * FROM some_table ORDER BY foo DESC"
103         $abstract->select('some_table', '*', undef, {order_by => {-desc => 'foo'}});
104
105   LIMIT/OFFSET
106       The "limit" and "offset" options can be used to generate "SELECT"
107       queries with "LIMIT" and "OFFSET" clauses.
108
109         # "SELECT * FROM some_table LIMIT 10"
110         $abstract->select('some_table', '*', undef, {limit => 10});
111
112         # "SELECT * FROM some_table OFFSET 5"
113         $abstract->select('some_table', '*', undef, {offset => 5});
114
115         # "SELECT * FROM some_table LIMIT 10 OFFSET 5"
116         $abstract->select('some_table', '*', undef, {limit => 10, offset => 5});
117
118   GROUP BY
119       The "group_by" option can be used to generate "SELECT" queries with
120       "GROUP BY" clauses. So far, array references to pass a list of fields
121       and scalar references to pass literal SQL are supported.
122
123         # "SELECT * FROM some_table GROUP BY foo, bar"
124         $abstract->select('some_table', '*', undef, {group_by => ['foo', 'bar']});
125
126         # "SELECT * FROM some_table GROUP BY foo, bar"
127         $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});
128
129   HAVING
130       The "having" option can be used to generate "SELECT" queries with
131       "HAVING" clauses, which takes the same values as the $where argument.
132
133         # "SELECT * FROM t GROUP BY a HAVING b = 'c'"
134         $abstract->select('t', '*', undef, {group_by => ['a'], having => {b => 'c'}});
135
136   FOR
137       The "for" option can be used to generate "SELECT" queries with "FOR"
138       clauses. So far, the scalar value "update" to pass "UPDATE" and scalar
139       references to pass literal SQL are supported.
140
141         # "SELECT * FROM some_table FOR UPDATE"
142         $abstract->select('some_table', '*', undef, {for => 'update'});
143
144         # "SELECT * FROM some_table FOR UPDATE SKIP LOCKED"
145         $abstract->select('some_table', '*', undef, {for => \'update skip locked'});
146

METHODS

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

AUTHOR

151       Sebastian Riedel, "sri@cpan.org".
152

COPYRIGHT AND LICENSE

154       Copyright (C) 2014-2021, Sebastian Riedel and others.
155
156       This program is free software, you can redistribute it and/or modify it
157       under the terms of the Artistic License version 2.0.
158