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->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 _get_pg_search_path {
44 # cache the search path as ['schema','schema',...] in the storage
46 $self->{_pg_search_path} ||= do {
48 my ($sp_string) = $dbh->selectrow_array('SHOW search_path');
49 while( $sp_string =~ s/("[^"]+"|[^,]+),?// ) {
50 unless( defined $1 and length $1 ) {
51 $self->throw_exception("search path sanity check failed: '$1'")
53 push @search_path, $1;
59 sub _dbh_get_autoinc_seq {
60 my ($self, $dbh, $schema, $table, $col) = @_;
62 # get the list of postgres schemas to search. if we have a schema
63 # specified, use that. otherwise, use the search path
65 if( defined $schema and length $schema ) {
66 @search_path = ( $schema );
68 @search_path = @{ $self->_get_pg_search_path($dbh) };
71 # find the sequence(s) of the column in question (should have nextval declared on it)
73 foreach my $search_schema (@search_path) {
74 my $info = $dbh->column_info(undef,$search_schema,$table,$col)->fetchrow_hashref;
75 if($info && defined $info->{COLUMN_DEF}
76 && $info->{COLUMN_DEF} =~ /^nextval\(+'([^']+)'::(?:text|regclass)\)/i
78 push @sequence_names, $1;
82 if (@sequence_names != 1) {
83 $self->throw_exception (sprintf
84 q|Unable to reliably determine autoinc sequence name for '%s'.'%s' (possible candidates: %s)|,
87 join (', ', (@sequence_names ? @sequence_names : 'none found') ),
91 my $seq = $sequence_names[0];
94 my $sth = $dbh->prepare (
95 'SELECT * FROM "information_schema"."sequences" WHERE "sequence_name" = ?'
100 while (my $h = $sth->fetchrow_hashref) {
101 push @seen_in_schemas, $h->{sequence_schema};
104 if (not @seen_in_schemas) {
105 $self->throw_exception (sprintf
106 q|Automatically determined autoinc sequence name '%s' for '%s'.'%s' does not seem to exist...'|,
112 elsif (@seen_in_schemas > 1) {
113 $self->throw_exception (sprintf
114 q|Unable to reliably fully-qualify automatically determined autoinc sequence name '%s' for '%s'.'%s' (same name exist in schemas: %s)|,
118 join (', ', (@seen_in_schemas)),
122 my $sql_maker = $self->sql_maker;
123 $seq = join ('.', map { $sql_maker->_quote ($_) } ($seen_in_schemas[0], $seq) );
130 sub get_autoinc_seq {
131 my ($self,$source,$col) = @_;
134 my $table = $source->name;
136 if (ref $table eq 'SCALAR') {
139 elsif ($table =~ /^(.+)\.(.+)$/) {
140 ($schema, $table) = ($1, $2);
143 $self->dbh_do('_dbh_get_autoinc_seq', $schema, $table, $col);
150 sub datetime_parser_type { return "DateTime::Format::Pg"; }
152 sub bind_attribute_by_data_type {
153 my ($self,$data_type) = @_;
155 my $bind_attributes = {
156 bytea => { pg_type => DBD::Pg::PG_BYTEA },
157 blob => { pg_type => DBD::Pg::PG_BYTEA },
160 if( defined $bind_attributes->{$data_type} ) {
161 return $bind_attributes->{$data_type};
168 sub _sequence_fetch {
169 my ( $self, $type, $seq ) = @_;
170 my ($id) = $self->_get_dbh->selectrow_array("SELECT nextval('${seq}')");
175 my ($self, $name) = @_;
177 $self->_get_dbh->pg_savepoint($name);
181 my ($self, $name) = @_;
183 $self->_get_dbh->pg_release($name);
187 my ($self, $name) = @_;
189 $self->_get_dbh->pg_rollback_to($name);
198 DBIx::Class::Storage::DBI::Pg - Automatic primary key class for PostgreSQL
202 # In your table classes
203 __PACKAGE__->load_components(qw/PK::Auto Core/);
204 __PACKAGE__->set_primary_key('id');
205 __PACKAGE__->sequence('mysequence');
209 This class implements autoincrements for PostgreSQL.
211 =head1 POSTGRESQL SCHEMA SUPPORT
213 This supports multiple PostgreSQL schemas, with one caveat: for
214 performance reasons, the schema search path is queried the first time it is
215 needed and CACHED for subsequent uses.
217 For this reason, you should do any necessary manipulation of the
218 PostgreSQL search path BEFORE instantiating your schema object, or as
219 part of the on_connect_do option to connect(), for example:
221 my $schema = My::Schema->connect
224 [ 'SET search_path TO myschema, foo, public' ],
230 See L<DBIx::Class/CONTRIBUTORS>
234 You may distribute this code under the same terms as Perl itself.