t/47postgres-producer.t

   1 #!/usr/bin/perl
   2
   3 use strict;
   4 use warnings;
   5
   6 use Test::More;
   7 use Test::Exception;
   8 use Test::SQL::Translator qw(maybe_plan);
   9 use SQL::Translator::Schema::Constants;
  10 use Data::Dumper;
  11 use FindBin qw/$Bin/;
  12
  13 # Testing 1,2,3,4...
  14 #=============================================================================
  15
  16 BEGIN {
  17     maybe_plan(53,
  18         'SQL::Translator::Producer::PostgreSQL',
  19         'Test::Differences',
  20     )
  21 }
  22 use Test::Differences;
  23 use SQL::Translator;
  24
  25 my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
  26
  27 {
  28   my $table = SQL::Translator::Schema::Table->new( name => 'foo.bar' );
  29   my $field = SQL::Translator::Schema::Field->new( name => 'baz',
  30                                                  table => $table,
  31                                                  data_type => 'VARCHAR',
  32                                                  size => 10,
  33                                                  default_value => 'quux',
  34                                                  is_auto_increment => 0,
  35                                                  is_nullable => 0,
  36                                                  is_foreign_key => 0,
  37                                                  is_unique => 0 );
  38   $table->add_field($field);
  39   my ($create, $fks) = SQL::Translator::Producer::PostgreSQL::create_table($table, { quote_table_names => q{"} });
  40   is($table->name, 'foo.bar');
  41
  42   my $expected = "--\n-- Table: foo.bar\n--\nCREATE TABLE \"foo\".\"bar\" (\n  \"baz\" character varying(10) DEFAULT 'quux' NOT NULL\n)";
  43   is($create, $expected);
  44 }
  45
  46 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
  47
  48 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
  49                                                   table => $table,
  50                                                   data_type => 'VARCHAR',
  51                                                   size => 10,
  52                                                   default_value => undef,
  53                                                   is_auto_increment => 0,
  54                                                   is_nullable => 1,
  55                                                   is_foreign_key => 0,
  56                                                   is_unique => 0 );
  57
  58 my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
  59
  60 is($field1_sql, 'myfield character varying(10)', 'Create field works');
  61
  62 my $field_array = SQL::Translator::Schema::Field->new( name => 'myfield',
  63                                                   table => $table,
  64                                                   data_type => 'character varying[]',
  65                                                   size => 10,
  66                                                   default_value => undef,
  67                                                   is_auto_increment => 0,
  68                                                   is_nullable => 1,
  69                                                   is_foreign_key => 0,
  70                                                   is_unique => 0 );
  71
  72 my $field_array_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_array);
  73
  74 is($field_array_sql, 'myfield character varying(10)[]', 'Create field works');
  75
  76 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
  77                                                   table => $table,
  78                                                   data_type => 'VARCHAR',
  79                                                   size      => 25,
  80                                                   default_value => undef,
  81                                                   is_auto_increment => 0,
  82                                                   is_nullable => 0,
  83                                                   is_foreign_key => 0,
  84                                                   is_unique => 0 );
  85
  86 my $pk_constraint = SQL::Translator::Schema::Constraint->new(
  87     table => $table,
  88     name   => 'foo',
  89     fields => [qw(myfield)],
  90     type   => 'PRIMARY_KEY',
  91 );
  92
  93 my  ($pk_constraint_def_ref, $pk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($pk_constraint);
  94 ok(@{$pk_constraint_def_ref} == 1 && @{$pk_constraint_fk_ref} == 0,  'precheck of create_Primary Key constraint');
  95 is($pk_constraint_def_ref->[0], 'CONSTRAINT foo PRIMARY KEY (myfield)', 'Create Primary Key Constraint works');
  96
  97 my $alter_pk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($pk_constraint);
  98 is($alter_pk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT foo', 'Alter drop Primary Key constraint works');
  99
 100 my $table2 = SQL::Translator::Schema::Table->new( name => 'mytable2');
 101
 102 my $field1_2 = SQL::Translator::Schema::Field->new( name => 'myfield_2',
 103                                                   table => $table,
 104                                                   data_type => 'VARCHAR',
 105                                                   size => 10,
 106                                                   default_value => undef,
 107                                                   is_auto_increment => 0,
 108                                                   is_nullable => 1,
 109                                                   is_foreign_key => 0,
 110                                                   is_unique => 0 );
 111
 112 # check named, and unnamed foreign keys
 113 for my $name ( 'foo', undef ) {
 114     my $fk_constraint = SQL::Translator::Schema::Constraint->new(
 115         table => $table,
 116         name   => $name,
 117         fields => [qw(myfield)],
 118         type   => 'FOREIGN_KEY',
 119         reference_table => $table2,
 120         reference_fields => [qw(myfield_2)],
 121     );
 122     my $fk_constraint_2 = SQL::Translator::Schema::Constraint->new(
 123         table => $table,
 124         name   => $name,
 125         fields => [qw(myfield)],
 126         type   => 'FOREIGN_KEY',
 127         reference_table => $table2,
 128         reference_fields => [qw(myfield_2)],
 129     );
 130
 131     my  ($fk_constraint_def_ref, $fk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($fk_constraint);
 132
 133     ok(@{$fk_constraint_def_ref} == 0 && @{$fk_constraint_fk_ref} == 1,  'precheck of create_Foreign Key constraint');
 134
 135     if ( $name ) {
 136         is($fk_constraint_fk_ref->[0], "ALTER TABLE mytable ADD CONSTRAINT $name FOREIGN KEY (myfield)
 137   REFERENCES mytable2 (myfield_2) DEFERRABLE", 'Create Foreign Key Constraint works');
 138
 139         # ToDo: may we should check if the constraint name was valid, or if next
 140         #       unused_name created has choosen a different one
 141         my $alter_fk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($fk_constraint);
 142         is($alter_fk_constraint, "ALTER TABLE mytable DROP CONSTRAINT $name", 'Alter drop Foreign Key constraint works');
 143     }
 144     else {
 145         is($fk_constraint_fk_ref->[0], 'ALTER TABLE mytable ADD FOREIGN KEY (myfield)
 146   REFERENCES mytable2 (myfield_2) DEFERRABLE', 'Create named Foreign Key Constraint works');
 147
 148         my $alter_fk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($fk_constraint);
 149         is($alter_fk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT mytable_myfield_fkey', 'Alter drop named Foreign Key constraint works');
 150     }
 151 }
 152
 153
 154 my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
 155                                                                 $field2);
 156 is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
 157 ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)],
 158  'Alter field works');
 159
 160 my $field1_complex = SQL::Translator::Schema::Field->new(
 161     name => 'my_complex_field',
 162     table => $table,
 163     data_type => 'VARCHAR',
 164     size => 10,
 165     default_value => undef,
 166                                                   is_auto_increment => 0,
 167     is_nullable => 1,
 168     is_foreign_key => 0,
 169     is_unique => 0
 170 );
 171
 172 my $field2_complex = SQL::Translator::Schema::Field->new(
 173     name      => 'my_altered_field',
 174     table => $table,
 175     data_type => 'VARCHAR',
 176     size      => 60,
 177     default_value => 'whatever',
 178     is_auto_increment => 0,
 179     is_nullable => 1,
 180     is_foreign_key => 0,
 181     is_unique => 0
 182 );
 183
 184 my $alter_field_complex = SQL::Translator::Producer::PostgreSQL::alter_field($field1_complex, $field2_complex);
 185 is(
 186     $alter_field_complex,
 187     q{ALTER TABLE mytable RENAME COLUMN my_complex_field TO my_altered_field;
 188 ALTER TABLE mytable ALTER COLUMN my_altered_field TYPE character varying(60);
 189 ALTER TABLE mytable ALTER COLUMN my_altered_field SET DEFAULT 'whatever'},
 190     'Complex Alter field works'
 191 );
 192
 193 $field1->name('field3');
 194 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
 195
 196 is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works');
 197
 198 my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2);
 199 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
 200
 201 my $field3 = SQL::Translator::Schema::Field->new( name      => 'time_field',
 202                                                   table => $table,
 203                                                   data_type => 'TIME',
 204                                                   default_value => undef,
 205                                                   is_auto_increment => 0,
 206                                                   is_nullable => 0,
 207                                                   is_foreign_key => 0,
 208                                                   is_unique => 0 );
 209
 210 my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3);
 211
 212 is($field3_sql, 'time_field time NOT NULL', 'Create time field works');
 213
 214 my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new(
 215     name      => 'datetime_with_TZ',
 216     table     => $table,
 217     data_type => 'timestamp with time zone',
 218     size      => 7,
 219 );
 220
 221 my $field3_datetime_with_TZ_sql =
 222     SQL::Translator::Producer::PostgreSQL::create_field(
 223         $field3_datetime_with_TZ
 224     );
 225
 226 is(
 227     $field3_datetime_with_TZ_sql,
 228     'datetime_with_TZ timestamp(6) with time zone',
 229     'Create time field with time zone and size, works'
 230 );
 231
 232 my $field3_time_without_TZ = SQL::Translator::Schema::Field->new(
 233     name      => 'time_without_TZ',
 234     table     => $table,
 235     data_type => 'time without time zone',
 236     size      => 2,
 237 );
 238
 239 my $field3_time_without_TZ_sql
 240     = SQL::Translator::Producer::PostgreSQL::create_field(
 241         $field3_time_without_TZ
 242     );
 243
 244 is(
 245     $field3_time_without_TZ_sql,
 246     'time_without_TZ time(2) without time zone',
 247     'Create time field without time zone but with size, works'
 248 );
 249
 250 my $field_num = SQL::Translator::Schema::Field->new( name => 'num',
 251                                                   table => $table,
 252                                                   data_type => 'numeric',
 253                                                   size => [10,2],
 254                                                   );
 255 my $fieldnum_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_num);
 256
 257 is($fieldnum_sql, 'num numeric(10,2)', 'Create numeric field works');
 258
 259
 260 my $field4 = SQL::Translator::Schema::Field->new( name      => 'bytea_field',
 261                                                   table => $table,
 262                                                   data_type => 'bytea',
 263                                                   size => '16777215',
 264                                                   default_value => undef,
 265                                                   is_auto_increment => 0,
 266                                                   is_nullable => 0,
 267                                                   is_foreign_key => 0,
 268                                                   is_unique => 0 );
 269
 270 my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4);
 271
 272 is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
 273
 274 my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field',
 275                                                    table => $table,
 276                                                    data_type => 'enum',
 277                                                    extra => { list => [ 'Foo', 'Bar' ] },
 278                                                    is_auto_increment => 0,
 279                                                    is_nullable => 0,
 280                                                    is_foreign_key => 0,
 281                                                    is_unique => 0 );
 282
 283 my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
 284
 285 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
 286
 287
 288
 289
 290 my $field6 = SQL::Translator::Schema::Field->new(
 291                                                   name      => 'character',
 292                                                   table => $table,
 293                                                   data_type => 'character',
 294                                                   size => '123',
 295                                                   default_value => 'foobar',
 296                                                     is_auto_increment => 0,
 297                                                     is_nullable => 0,
 298                                                     is_foreign_key => 0,
 299                                                     is_unique => 0);
 300
 301 my $field7 = SQL::Translator::Schema::Field->new(
 302                                 name      => 'character',
 303                                 table => $table,
 304                                 data_type => 'character',
 305                                 size => '123',
 306                                 default_value => undef,
 307                                   is_auto_increment => 0,
 308                                   is_nullable => 0,
 309                                   is_foreign_key => 0,
 310                                   is_unique => 0);
 311
 312 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 313                                                                 $field7);
 314
 315 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT');
 316
 317 $field7->default_value(q(foo'bar'));
 318
 319 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 320                                                                 $field7);
 321
 322 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping');
 323
 324 $field7->default_value(\q(foobar));
 325
 326 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 327                                                                 $field7);
 328
 329 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref');
 330
 331 $field7->is_nullable(1);
 332 $field7->default_value(q(foobar));
 333
 334 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 335                                                                 $field7);
 336
 337 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL');
 338
 339 my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field',
 340                                                    table => $table,
 341                                                    data_type => 'timestamp with time zone',
 342                                                    size => 6,
 343                                                    is_auto_increment => 0,
 344                                                    is_nullable => 0,
 345                                                    is_foreign_key => 0,
 346                                                    is_unique => 0 );
 347
 348 my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 });
 349
 350 is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision');
 351
 352 my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field',
 353                                                    table => $table,
 354                                                    data_type => 'time with time zone',
 355                                                    size => 6,
 356                                                    is_auto_increment => 0,
 357                                                    is_nullable => 0,
 358                                                    is_foreign_key => 0,
 359                                                    is_unique => 0 );
 360
 361 my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 });
 362
 363 is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision');
 364
 365 my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field',
 366                                                    table => $table,
 367                                                    data_type => 'interval',
 368                                                    size => 6,
 369                                                    is_auto_increment => 0,
 370                                                    is_nullable => 0,
 371                                                    is_foreign_key => 0,
 372                                                    is_unique => 0 );
 373
 374 my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 });
 375
 376 is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision');
 377
 378
 379 my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field',
 380                                                    table => $table,
 381                                                    data_type => 'time without time zone',
 382                                                    size => 6,
 383                                                    is_auto_increment => 0,
 384                                                    is_nullable => 0,
 385                                                    is_foreign_key => 0,
 386                                                    is_unique => 0 );
 387
 388 my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 });
 389
 390 is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision');
 391
 392
 393
 394 my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field',
 395                                                    table => $table,
 396                                                    data_type => 'timestamp',
 397                                                    is_auto_increment => 0,
 398                                                    is_nullable => 0,
 399                                                    is_foreign_key => 0,
 400                                                    is_unique => 0 );
 401
 402 my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 });
 403
 404 is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision');
 405
 406 my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name',
 407                                                    table => $table,
 408                                                    data_type => 'enum',
 409                                                    extra => { list => [ 'Foo', 'Bar' ],
 410                                                               custom_type_name => 'real_enum_type' },
 411                                                    is_auto_increment => 0,
 412                                                    is_nullable => 0,
 413                                                    is_foreign_key => 0,
 414                                                    is_unique => 0 );
 415
 416 my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field($field13,{ postgres_version => 8.3 });
 417
 418 is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works');
 419
 420
 421 {
 422     # let's test default values! -- rjbs, 2008-09-30
 423     my %field = (
 424         table => $table,
 425         data_type => 'VARCHAR',
 426         size => 10,
 427         is_auto_increment => 0,
 428         is_nullable => 1,
 429         is_foreign_key => 0,
 430         is_unique => 0,
 431     );
 432
 433     {
 434         my $simple_default = SQL::Translator::Schema::Field->new(
 435             %field,
 436             name => 'str_default',
 437             default_value => 'foo',
 438         );
 439
 440         is(
 441             $PRODUCER->($simple_default),
 442             q{str_default character varying(10) DEFAULT 'foo'},
 443             'default str',
 444         );
 445     }
 446
 447     {
 448         my $null_default = SQL::Translator::Schema::Field->new(
 449             %field,
 450             name => 'null_default',
 451             default_value => \'NULL',
 452         );
 453
 454         is(
 455             $PRODUCER->($null_default),
 456             q{null_default character varying(10) DEFAULT NULL},
 457             'default null',
 458         );
 459     }
 460
 461     {
 462         my $null_default = SQL::Translator::Schema::Field->new(
 463             %field,
 464             name => 'null_default_2',
 465             default_value => 'NULL', # XXX: this should go away
 466         );
 467
 468         is(
 469             $PRODUCER->($null_default),
 470             q{null_default_2 character varying(10) DEFAULT NULL},
 471             'default null from special cased string',
 472         );
 473     }
 474
 475     {
 476         my $func_default = SQL::Translator::Schema::Field->new(
 477             %field,
 478             name => 'func_default',
 479             default_value => \'func(funky)',
 480         );
 481
 482         is(
 483             $PRODUCER->($func_default),
 484             q{func_default character varying(10) DEFAULT func(funky)},
 485             'unquoted default from scalar ref',
 486         );
 487     }
 488 }
 489
 490
 491 my $view1 = SQL::Translator::Schema::View->new(
 492     name   => 'view_foo',
 493     fields => [qw/id name/],
 494     sql    => 'SELECT id, name FROM thing',
 495 );
 496 my $create_opts = { add_replace_view => 1, no_comments => 1 };
 497 my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts);
 498
 499 my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS
 500     SELECT id, name FROM thing
 501 ";
 502 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
 503
 504 my $view2 = SQL::Translator::Schema::View->new(
 505     name   => 'view_foo2',
 506     sql    => 'SELECT id, name FROM thing',
 507     extra  => {
 508       'temporary'    => '1',
 509       'check_option' => 'cascaded',
 510     },
 511 );
 512 my $create2_opts = { add_replace_view => 1, no_comments => 1 };
 513 my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);
 514
 515 my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS
 516     SELECT id, name FROM thing
 517  WITH CASCADED CHECK OPTION";
 518 is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');
 519
 520 {
 521     my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => [qw( foo  bar )] );
 522     my $quote = { quote_table_names => '"', quote_field_names => '"' };
 523
 524     {
 525         my $index = $table->add_index(name => 'myindex', fields => ['foo']);
 526         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 527         is($def, "CREATE INDEX myindex on foobar (foo)", 'index created');
 528         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 529         is($def, 'CREATE INDEX "myindex" on "foobar" ("foo")', 'index created w/ quotes');
 530     }
 531
 532     {
 533         my $index = $table->add_index(name => 'myindex', fields => ['lower(foo)']);
 534         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 535         is($def, "CREATE INDEX myindex on foobar (lower(foo))", 'index created');
 536         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 537         is($def, 'CREATE INDEX "myindex" on "foobar" (lower(foo))', 'index created w/ quotes');
 538     }
 539
 540     {
 541         my $index = $table->add_index(name => 'myindex', fields => ['bar', 'lower(foo)']);
 542         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 543         is($def, "CREATE INDEX myindex on foobar (bar, lower(foo))", 'index created');
 544         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 545         is($def, 'CREATE INDEX "myindex" on "foobar" ("bar", lower(foo))', 'index created w/ quotes');
 546     }
 547
 548     {
 549         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['foo']);
 550         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 551         is($def->[0], 'CONSTRAINT constr UNIQUE (foo)', 'constraint created');
 552         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 553         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("foo")', 'constraint created w/ quotes');
 554     }
 555
 556     {
 557         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['lower(foo)']);
 558         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 559         is($def->[0], 'CONSTRAINT constr UNIQUE (lower(foo))', 'constraint created');
 560         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 561         is($def->[0], 'CONSTRAINT "constr" UNIQUE (lower(foo))', 'constraint created w/ quotes');
 562     }
 563
 564     {
 565         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['bar', 'lower(foo)']);
 566         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 567         is($def->[0], 'CONSTRAINT constr UNIQUE (bar, lower(foo))', 'constraint created');
 568         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 569         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("bar", lower(foo))', 'constraint created w/ quotes');
 570     }
 571 }
 572
 573 my $drop_view_opts1 = { add_drop_view => 1, no_comments => 1, postgres_version => 8.001 };
 574 my $drop_view_8_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts1);
 575
 576 my $drop_view_8_1_expected = "DROP VIEW view_foo;
 577 CREATE VIEW view_foo ( id, name ) AS
 578     SELECT id, name FROM thing
 579 ";
 580
 581 is($drop_view_8_1_produced, $drop_view_8_1_expected, "My DROP VIEW statement for 8.1 is correct");
 582
 583 my $drop_view_opts2 = { add_drop_view => 1, no_comments => 1, postgres_version => 9.001 };
 584 my $drop_view_9_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts2);
 585
 586 my $drop_view_9_1_expected = "DROP VIEW IF EXISTS view_foo;
 587 CREATE VIEW view_foo ( id, name ) AS
 588     SELECT id, name FROM thing
 589 ";
 590
 591 is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct");