1 package DBIx::Class::Storage::DBI::Pg;
6 use base qw/DBIx::Class::Storage::DBI::MultiColumnIn/;
9 use DBD::Pg qw(:pg_types);
11 # Ask for a DBD::Pg with array support
12 warn "DBD::Pg 2.9.2 or greater is strongly recommended\n"
13 if ($DBD::Pg::VERSION < 2.009002); # pg uses (used?) version::qv()
15 sub with_deferred_fk_checks {
16 my ($self, $sub) = @_;
18 $self->_get_dbh->do('SET CONSTRAINTS ALL DEFERRED');
23 my ($self,$source,$col) = @_;
24 my $seq = ( $source->column_info($col)->{sequence} ||= $self->dbh_do('_dbh_get_autoinc_seq', $source, $col) )
25 or $self->throw_exception( "could not determine sequence for "
27 . ".$col, please consider adding a "
28 . "schema-qualified sequence to its column info"
31 $self->_dbh_last_insert_id ($self->_dbh, $seq);
34 # there seems to be absolutely no reason to have this as a separate method,
35 # but leaving intact in case someone is already overriding it
36 sub _dbh_last_insert_id {
37 my ($self, $dbh, $seq) = @_;
38 $dbh->last_insert_id(undef, undef, undef, undef, {sequence => $seq});
42 sub _dbh_get_autoinc_seq {
43 my ($self, $dbh, $source, $col) = @_;
46 my $table = $source->name;
48 # deref table name if it needs it
50 if ref $table eq 'SCALAR';
52 # parse out schema name if present
53 if( $table =~ /^(.+)\.(.+)$/ ) {
54 ( $schema, $table ) = ( $1, $2 );
57 # Build and execute a query into the pg_catalog to find the Pg
58 # expression for the default value for this column in this table.
59 # If the table name is schema-qualified, query using that specific
62 # Otherwise, find the table in the standard Postgres way, using the
63 # search path. This is done with the pg_catalog.pg_table_is_visible
64 # function, which returns true if a given table is 'visible',
65 # meaning the first table of that name to be found in the search
68 # I *think* we can be assured that this query will always find the
69 # correct column according to standard Postgres semantics.
73 my $sqlmaker = $self->sql_maker;
74 local $sqlmaker->{bindtype} = 'normal';
76 my ($where, @bind) = $sqlmaker->where ({
77 'a.attnum' => {'>', 0},
78 'c.relname' => $table,
80 -not_bool => 'a.attisdropped',
81 (defined $schema && length $schema)
82 ? ( 'n.nspname' => $schema )
83 : ( -bool => \'pg_catalog.pg_table_is_visible(c.oid)' )
86 my ($seq_expr) = $dbh->selectrow_array(<<EOS,undef,@bind);
89 (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
90 FROM pg_catalog.pg_attrdef d
91 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
92 FROM pg_catalog.pg_class c
93 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
94 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
99 # if no default value is set on the column, or if we can't parse the
100 # default value as a sequence, throw.
101 unless ( defined $seq_expr and $seq_expr =~ /^nextval\(+'([^']+)'::(?:text|regclass)\)/i ){
102 $seq_expr = '' unless defined $seq_expr;
103 $schema = "$schema." if defined $schema && length $schema;
104 $self->throw_exception( "no sequence found for $schema$table.$col, check table definition, "
105 . "or explicitly set the 'sequence' for this column in the "
106 . $source->source_name
114 sub get_autoinc_seq {
115 my ($self,$source,$col) = @_;
123 sub datetime_parser_type { return "DateTime::Format::Pg"; }
125 sub bind_attribute_by_data_type {
126 my ($self,$data_type) = @_;
128 my $bind_attributes = {
129 bytea => { pg_type => DBD::Pg::PG_BYTEA },
130 blob => { pg_type => DBD::Pg::PG_BYTEA },
133 if( defined $bind_attributes->{$data_type} ) {
134 return $bind_attributes->{$data_type};
141 sub _sequence_fetch {
142 my ( $self, $type, $seq ) = @_;
143 my ($id) = $self->_get_dbh->selectrow_array("SELECT nextval('${seq}')");
148 my ($self, $name) = @_;
150 $self->_get_dbh->pg_savepoint($name);
154 my ($self, $name) = @_;
156 $self->_get_dbh->pg_release($name);
160 my ($self, $name) = @_;
162 $self->_get_dbh->pg_rollback_to($name);
171 DBIx::Class::Storage::DBI::Pg - Automatic primary key class for PostgreSQL
175 # In your table classes
176 __PACKAGE__->load_components(qw/PK::Auto Core/);
177 __PACKAGE__->set_primary_key('id');
178 __PACKAGE__->sequence('mysequence');
182 This class implements autoincrements for PostgreSQL.
184 =head1 POSTGRESQL SCHEMA SUPPORT
186 This driver supports multiple PostgreSQL schemas, with one caveat: for
187 performance reasons, data about the search path, sequence names, and
188 so forth is queried as needed and CACHED for subsequent uses.
190 For this reason, once your schema is instantiated, you should not
191 change the PostgreSQL schema search path for that schema's database
192 connection. If you do, Bad Things may happen.
194 You should do any necessary manipulation of the search path BEFORE
195 instantiating your schema object, or as part of the on_connect_do
196 option to connect(), for example:
198 my $schema = My::Schema->connect
201 [ 'SET search_path TO myschema, foo, public' ],
207 See L<DBIx::Class/CONTRIBUTORS>
211 You may distribute this code under the same terms as Perl itself.