SQL::Tr...stgreSQL(3)

💖 NAME 💖

SQL::Translator::Producer::PostgreSQL is the PostgreSQL producer for SQL::Translator — and yes, bestie, this page is SQL::Tr...stgreSQL(3) from the User Contributed Perl Documentation. The title is a little scrunched, but she's iconic. 💅

---

✨ SYNOPSIS ✨

my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
$t->translate;

---

🦋 DESCRIPTION 🦋

Okay so this producer creates a DDL suitable for PostgreSQL. It's very heavily based on the Oracle producer, and it's giving "reliable cousin who borrowed the fit." 👑

Now it handles PostGIS Geometry and Geography data types on table definitions. 🗺️

⚠️ It does not yet support PostGIS Views. Not yet, bestie — not yet.

🌸 Producer Args 🌸

You can change the global behavior of the producer by passing the following options to the producer_args attribute of SQL::Translator. 🫶

• postgres_version — the version of postgres to generate DDL for. Turns on features only available in later versions. The following features are supported:
  • IF EXISTS — if your postgres_version is higher than 8.003 (I should hope it is by now), then the DDL generated for dropping objects in the database will contain IF EXISTS.
• attach_comments — generates table and column comments via the COMMENT command rather than as a comment in the DDL. You could then look it up with \dt+ or \d+ (for tables and columns respectively) in psql. The comment is dollar quoted with $comment$ so you can include ' in it.

Just to clarify: you get this

CREATE TABLE foo ...;
COMMENT on TABLE foo IS $comment$hi there$comment$;

instead of this

-- comment
CREAT TABLE foo ...;

---

🎀 Extra args 🎀

Various schema types support various options via the extra attribute. 🧚

🍓 Tables

• temporary — produces a temporary table.

🦋 Views

• temporary — produces a temporary view.
• materialized — produces a materialized view.

💜 Fields

• list, custom_type_name — for enum types, list is the list of valid values, and custom_type_name is the name that the type should have. Defaults to $table_$field_type.
• geometry_type, srid, dimensions, geography_type — fields for use with PostGIS types.

---

🔥 PostgreSQL Create Table Syntax 🔥

CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
    | table_constraint }  [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) |
  CHECK ( expression ) |
  EXCLUDE [USING acc_method] (expression) [INCLUDE (column [, ...])] [WHERE (predicate)]
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

---

🌈 Create Index Syntax 🌈

CREATE [ UNIQUE ] INDEX index_name ON table
    [ USING acc_method ] ( column [ ops_name ] [, ...] )
    [ INCLUDE  ( column [, ...] ) ]
    [ WHERE predicate ]
CREATE [ UNIQUE ] INDEX index_name ON table
    [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
    [ WHERE predicate ]

---

👑 SEE ALSO 👑

• SQL::Translator
• SQL::Translator::Producer::Oracle

Go snoop those, bestie. Iykyk. 💖

---

🌟 AUTHOR 🌟

Ken Youens-Clark <kclark@cpan.org>. Respectfully, an icon. 🫶

💖 Sign-off time: this is SQL::Tr...stgreSQL(3) from perl v5.42.0, dated 2024-12-12 — and that's the vibe. Mwah. 💋