.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40) .\" .\" 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 "2021-02-18" "perl v5.32.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, .