SQL::Abstract::Pg(3)

💖 NAME 💖

okay bestie, this is SQL::Abstract::Pg(3) — literally SQL::Abstract::Pg: PostgreSQL features for SQL::Abstract. It's the pg-flavored glow-up, no cap. 🐘✨

🧚 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 features used by Mojo::Pg. Same abstract energy, now with pg extras. 💅

🍓 JSON 🍓

In many places (as supported by SQL::Abstract) you can use the -json unary op to encode JSON from Perl data structures. It's giving "Perl data in, JSON string out." 🫶

# "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, these are the supported flavors:

• undef to pass DO NOTHING
• array references to pass DO UPDATE with conflict targets and a SET expression
• scalar references to pass literal SQL
• array reference references to pass literal SQL with bind values

# "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". Yes bestie, upsert szn. 👑

# "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 array references with field names and aliases, as well as array references containing scalar references to pass literal SQL and array reference references to pass literal SQL with bind values. Basically: aliases and literal-SQL moments are invited to the party. 🥳

# "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 only table names, but also array references with tables to generate JOIN clauses for. It's giving "tables, but make it a join." 🔗

# "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 now also pass a hash reference with various options. This includes order_by, which takes the same values as the $order argument. Sort it out, bestie. 💫

# "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 queries with LIMIT and OFFSET clauses. Tiny slice, big vibe. 🍰

# "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 scalar references to pass literal SQL are supported. Group chat, but SQL. 💬

# "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. It's the after-party filter. 🎉

# "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 to pass literal SQL are supported. Lock it down, bestie. 🔐

# "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. Zero extra method drama. 💖

✍️ AUTHOR ✍️

Sebastian Riedel, sri@cpan.org.

📜 COPYRIGHT AND LICENSE 📜

Copyright (C) 2014-2021, Sebastian Riedel and others.

This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0.

👀 SEE ALSO 👀

Go peek at <https://github.com/mojolicious/sql-abstract-pg>, Mojolicious::Guides, and <https://mojolicious.org>. 🫶

k bye bestie, this page was served with perl v5.42.0 on 2025-02-06 — stay slay and may your queries always be indexed 💖💅