.\" Automatically generated by Pod::Man 4.10 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "SQL::Abstract::Pg 3pm" .TH SQL::Abstract::Pg 3pm "2019-01-21" "perl v5.28.1" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" SQL::Abstract::Pg \- PostgreSQL .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 1 \& use SQL::Abstract::Pg; \& \& my $abstract = SQL::Abstract::Pg\->new; \& say $abstract\->select(\*(Aqsome_table\*(Aq); .Ve .SH "DESCRIPTION" .IX Header "DESCRIPTION" SQL::Abstract::Pg extends SQL::Abstract with a few PostgreSQL features used by Mojo::Pg. .SS "\s-1JSON\s0" .IX Subsection "JSON" In many places (as supported by SQL::Abstract) you can use the \f(CW\*(C`\-json\*(C'\fR unary op to encode \s-1JSON\s0 from Perl data structures. .PP .Vb 2 \& # "update some_table set foo = \*(Aq[1,2,3]\*(Aq where bar = 23" \& $abstract\->update(\*(Aqsome_table\*(Aq, {foo => {\-json => [1, 2, 3]}}, {bar => 23}); \& \& # "select * from some_table where foo = \*(Aq[1,2,3]\*(Aq" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, {foo => {\*(Aq=\*(Aq => {\-json => [1, 2, 3]}}}); .Ve .SH "INSERT" .IX Header "INSERT" .Vb 1 \& $abstract\->insert($table, \e@values || \e%fieldvals, \e%options); .Ve .SS "\s-1ON CONFLICT\s0" .IX Subsection "ON CONFLICT" The \f(CW\*(C`on_conflict\*(C'\fR option can be used to generate \f(CW\*(C`INSERT\*(C'\fR queries with \&\f(CW\*(C`ON CONFLICT\*(C'\fR clauses. So far \f(CW\*(C`undef\*(C'\fR to pass \f(CW\*(C`DO NOTHING\*(C'\fR, array references to pass \f(CW\*(C`DO UPDATE\*(C'\fR with conflict targets and a \f(CW\*(C`SET\*(C'\fR expression, scalar references to pass literal \s-1SQL\s0 and array reference references to pass literal \&\s-1SQL\s0 with bind values are supported. .PP .Vb 2 \& # "insert into t (a) values (\*(Aqb\*(Aq) on conflict do nothing" \& $abstract\->insert(\*(Aqt\*(Aq, {a => \*(Aqb\*(Aq}, {on_conflict => undef}); \& \& # "insert into t (a) values (\*(Aqb\*(Aq) on conflict do nothing" \& $abstract\->insert(\*(Aqt\*(Aq, {a => \*(Aqb\*(Aq}, {on_conflict => \e\*(Aqdo nothing\*(Aq}); .Ve .PP This includes operations commonly referred to as \f(CW\*(C`upsert\*(C'\fR. .PP .Vb 2 \& # "insert into t (a) values (\*(Aqb\*(Aq) on conflict (a) do update set a = \*(Aqc\*(Aq" \& $abstract\->insert(\*(Aqt\*(Aq, {a => \*(Aqb\*(Aq}, {on_conflict => [a => {a => \*(Aqc\*(Aq}]}); \& \& # "insert into t (a, b) values (\*(Aqc\*(Aq, \*(Aqd\*(Aq) \& # on conflict (a, b) do update set a = \*(Aqe\*(Aq" \& $abstract\->insert( \& \*(Aqt\*(Aq, {a => \*(Aqc\*(Aq, b => \*(Aqd\*(Aq}, {on_conflict => [[\*(Aqa\*(Aq, \*(Aqb\*(Aq] => {a => \*(Aqe\*(Aq}]}); \& \& # "insert into t (a) values (\*(Aqb\*(Aq) on conflict (a) do update set a = \*(Aqc\*(Aq" \& $abstract\->insert( \& \*(Aqt\*(Aq, {a => \*(Aqb\*(Aq}, {on_conflict => \e[\*(Aq(a) do update set a = ?\*(Aq, \*(Aqc\*(Aq]}); .Ve .SH "SELECT" .IX Header "SELECT" .Vb 2 \& $abstract\->select($source, $fields, $where, $order); \& $abstract\->select($source, $fields, $where, \e%options); .Ve .SS "\s-1AS\s0" .IX Subsection "AS" The \f(CW$fields\fR 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 \s-1SQL\s0 and array reference references to pass literal \s-1SQL\s0 with bind values. .PP .Vb 2 \& # "select foo as bar from some_table" \& $abstract\->select(\*(Aqsome_table\*(Aq, [[foo => \*(Aqbar\*(Aq]]); \& \& # "select foo, bar as baz, yada from some_table" \& $abstract\->select(\*(Aqsome_table\*(Aq, [\*(Aqfoo\*(Aq, [bar => \*(Aqbaz\*(Aq], \*(Aqyada\*(Aq]); \& \& # "select extract(epoch from foo) as foo, bar from some_table" \& $abstract\->select(\*(Aqsome_table\*(Aq, [\e\*(Aqextract(epoch from foo) as foo\*(Aq, \*(Aqbar\*(Aq]); \& \& # "select \*(Aqtest\*(Aq as foo, bar from some_table" \& $abstract\->select(\*(Aqsome_table\*(Aq, [\e[\*(Aq? as foo\*(Aq, \*(Aqtest\*(Aq], \*(Aqbar\*(Aq]); .Ve .SS "\s-1JOIN\s0" .IX Subsection "JOIN" The \f(CW$source\fR argument now also accepts array references containing not only table names, but also array references with tables to generate \f(CW\*(C`JOIN\*(C'\fR clauses for. .PP .Vb 2 \& # "select * from foo join bar on (bar.foo_id = foo.id)" \& $abstract\->select([\*(Aqfoo\*(Aq, [\*(Aqbar\*(Aq, foo_id => \*(Aqid\*(Aq]]); \& \& # "select * from foo join bar on (foo.id = bar.foo_id)" \& $abstract\->select([\*(Aqfoo\*(Aq, [\*(Aqbar\*(Aq, \*(Aqfoo.id\*(Aq => \*(Aqbar.foo_id\*(Aq]]); \& \& # "select * from a join b on (b.a_id = a.id) join c on (c.a_id = a.id)" \& $abstract\->select([\*(Aqa\*(Aq, [\*(Aqb\*(Aq, a_id => \*(Aqid\*(Aq], [\*(Aqc\*(Aq, a_id => \*(Aqid\*(Aq]]); \& \& # "select * from foo left join bar on (bar.foo_id = foo.id)" \& $abstract\->select([\*(Aqfoo\*(Aq, [\-left => \*(Aqbar\*(Aq, foo_id => \*(Aqid\*(Aq]]); \& \& # "select * from a left join b on (b.a_id = a.id and b.a_id2 = a.id2)" \& $abstract\->select([\*(Aqa\*(Aq, [\-left => \*(Aqb\*(Aq, a_id => \*(Aqid\*(Aq, a_id2 => \*(Aqid2\*(Aq]]); .Ve .SS "\s-1ORDER BY\s0" .IX Subsection "ORDER BY" Alternatively to the \f(CW$order\fR argument accepted by SQL::Abstract you can now also pass a hash reference with various options. This includes \f(CW\*(C`order_by\*(C'\fR, which takes the same values as the \f(CW$order\fR argument. .PP .Vb 2 \& # "select * from some_table order by foo desc" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {order_by => {\-desc => \*(Aqfoo\*(Aq}}); .Ve .SS "\s-1LIMIT/OFFSET\s0" .IX Subsection "LIMIT/OFFSET" The \f(CW\*(C`limit\*(C'\fR and \f(CW\*(C`offset\*(C'\fR options can be used to generate \f(CW\*(C`SELECT\*(C'\fR queries with \f(CW\*(C`LIMIT\*(C'\fR and \f(CW\*(C`OFFSET\*(C'\fR clauses. .PP .Vb 2 \& # "select * from some_table limit 10" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {limit => 10}); \& \& # "select * from some_table offset 5" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {offset => 5}); \& \& # "select * from some_table limit 10 offset 5" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {limit => 10, offset => 5}); .Ve .SS "\s-1GROUP BY\s0" .IX Subsection "GROUP BY" The \f(CW\*(C`group_by\*(C'\fR option can be used to generate \f(CW\*(C`SELECT\*(C'\fR queries with \&\f(CW\*(C`GROUP BY\*(C'\fR clauses. So far array references to pass a list of fields and scalar references to pass literal \s-1SQL\s0 are supported. .PP .Vb 2 \& # "select * from some_table group by foo, bar" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {group_by => [\*(Aqfoo\*(Aq, \*(Aqbar\*(Aq]}); \& \& # "select * from some_table group by foo, bar" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {group_by => \e\*(Aqfoo, bar\*(Aq}); .Ve .SS "\s-1HAVING\s0" .IX Subsection "HAVING" The \f(CW\*(C`having\*(C'\fR option can be used to generate \f(CW\*(C`SELECT\*(C'\fR queries with \f(CW\*(C`HAVING\*(C'\fR clauses, which takes the same values as the \f(CW$where\fR argument. .PP .Vb 2 \& # "select * from t group by a having b = \*(Aqc\*(Aq" \& $abstract\->select(\*(Aqt\*(Aq, \*(Aq*\*(Aq, undef, {group_by => [\*(Aqa\*(Aq], having => {b => \*(Aqc\*(Aq}}); .Ve .SS "\s-1FOR\s0" .IX Subsection "FOR" The \f(CW\*(C`for\*(C'\fR option can be used to generate \f(CW\*(C`SELECT\*(C'\fR queries with \f(CW\*(C`FOR\*(C'\fR clauses. So far the scalar value \f(CW\*(C`update\*(C'\fR to pass \f(CW\*(C`UPDATE\*(C'\fR and scalar references to pass literal \s-1SQL\s0 are supported. .PP .Vb 2 \& # "select * from some_table for update" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {for => \*(Aqupdate\*(Aq}); \& \& # "select * from some_table for update skip locked" \& $abstract\->select(\*(Aqsome_table\*(Aq, \*(Aq*\*(Aq, undef, {for => \e\*(Aqupdate skip locked\*(Aq}); .Ve .SH "METHODS" .IX Header "METHODS" SQL::Abstract::Pg inherits all methods from SQL::Abstract. .SH "SEE ALSO" .IX Header "SEE ALSO" Mojo::Pg, Mojolicious::Guides, .