module Sequel::Postgres::DatabaseMethods

    # File lib/sequel/adapters/shared/postgres.rb
296 def add_conversion_proc(oid, callable=nil, &block)
297   conversion_procs[oid] = callable || block
298 end

    # File lib/sequel/adapters/shared/postgres.rb
303 def add_named_conversion_proc(name, &block)
304   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
305     raise Error, "No matching type in pg_type for #{name.inspect}"
306   end
307   add_conversion_proc(oid, block)
308 end

    # File lib/sequel/adapters/shared/postgres.rb
319 def check_constraints(table)
320   m = output_identifier_meth
321 
322   rows = metadata_dataset.
323     from{pg_constraint.as(:co)}.
324     left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
325     where(:conrelid=>regclass_oid(table), :contype=>'c').
326     select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
327 
328   hash = {}
329   rows.each do |row|
330     constraint = m.call(row[:constraint])
331     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
332     entry[:columns] << m.call(row[:column]) if row[:column]
333   end
334   
335   hash
336 end

    # File lib/sequel/adapters/shared/postgres.rb
310 def commit_prepared_transaction(transaction_id, opts=OPTS)
311   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
312 end

    # File lib/sequel/adapters/shared/postgres.rb
356 def convert_serial_to_identity(table, opts=OPTS)
357   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
358 
359   server = opts[:server]
360   server_hash = server ? {:server=>server} : OPTS
361   ds = dataset
362   ds = ds.server(server) if server
363 
364   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
365 
366   table_oid = regclass_oid(table)
367   im = input_identifier_meth
368   unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
369     raise Error, "could not determine column to convert from serial to identity automatically"
370   end
371 
372   column_num = ds.from(:pg_attribute).
373     where(:attrelid=>table_oid, :attname=>column).
374     get(:attnum)
375 
376   pg_class = Sequel.cast('pg_class', :regclass)
377   res = ds.from(:pg_depend).
378     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
379     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
380 
381   case res.length
382   when 0
383     raise Error, "unable to find related sequence when converting serial to identity"
384   when 1
385     seq_oid, already_identity = res.first
386   else
387     raise Error, "more than one linked sequence found when converting serial to identity"
388   end
389 
390   return if already_identity
391 
392   transaction(server_hash) do
393     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
394 
395     ds.from(:pg_depend).
396       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
397       update(:deptype=>'i')
398 
399     ds.from(:pg_attribute).
400       where(:attrelid=>table_oid, :attname=>column).
401       update(:attidentity=>'d')
402   end
403 
404   remove_cached_schema(table)
405   nil
406 end

    # File lib/sequel/adapters/shared/postgres.rb
428 def create_function(name, definition, opts=OPTS)
429   self << create_function_sql(name, definition, opts)
430 end

    # File lib/sequel/adapters/shared/postgres.rb
439 def create_language(name, opts=OPTS)
440   self << create_language_sql(name, opts)
441 end

    # File lib/sequel/adapters/shared/postgres.rb
448 def create_schema(name, opts=OPTS)
449   self << create_schema_sql(name, opts)
450 end

    # File lib/sequel/adapters/shared/postgres.rb
453 def create_table(name, options=OPTS, &block)
454   if options[:partition_of]
455     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
456     return
457   end
458 
459   super
460 end

    # File lib/sequel/adapters/shared/postgres.rb
463 def create_table?(name, options=OPTS, &block)
464   if options[:partition_of]
465     create_table(name, options.merge!(:if_not_exists=>true), &block)
466     return
467   end
468 
469   super
470 end

    # File lib/sequel/adapters/shared/postgres.rb
483 def create_trigger(table, name, function, opts=OPTS)
484   self << create_trigger_sql(table, name, function, opts)
485 end

    # File lib/sequel/adapters/shared/postgres.rb
487 def database_type
488   :postgres
489 end

    # File lib/sequel/adapters/shared/postgres.rb
496 def do(code, opts=OPTS)
497   language = opts[:language]
498   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
499 end

    # File lib/sequel/adapters/shared/postgres.rb
507 def drop_function(name, opts=OPTS)
508   self << drop_function_sql(name, opts)
509 end

    # File lib/sequel/adapters/shared/postgres.rb
516 def drop_language(name, opts=OPTS)
517   self << drop_language_sql(name, opts)
518 end

    # File lib/sequel/adapters/shared/postgres.rb
525 def drop_schema(name, opts=OPTS)
526   self << drop_schema_sql(name, opts)
527 end

    # File lib/sequel/adapters/shared/postgres.rb
535 def drop_trigger(table, name, opts=OPTS)
536   self << drop_trigger_sql(table, name, opts)
537 end

    # File lib/sequel/adapters/shared/postgres.rb
549 def foreign_key_list(table, opts=OPTS)
550   m = output_identifier_meth
551   schema, _ = opts.fetch(:schema, schema_and_table(table))
552   oid = regclass_oid(table)
553   reverse = opts[:reverse]
554 
555   if reverse
556     ctable = Sequel[:att2]
557     cclass = Sequel[:cl2]
558     rtable = Sequel[:att]
559     rclass = Sequel[:cl]
560   else
561     ctable = Sequel[:att]
562     cclass = Sequel[:cl]
563     rtable = Sequel[:att2]
564     rclass = Sequel[:cl2]
565   end
566 
567   if server_version >= 90500
568     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
569     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
570   else
571     range = 0...32
572     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
573     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
574   end
575 
576   ds = metadata_dataset.
577     from{pg_constraint.as(:co)}.
578     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
579     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
580     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
581     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
582     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
583     order{[co[:conname], cpos]}.
584     where{{
585       cl[:relkind]=>'r',
586       co[:contype]=>'f',
587       cl[:oid]=>oid,
588       cpos=>rpos
589     }}.
590     select{[
591       co[:conname].as(:name),
592       ctable[:attname].as(:column),
593       co[:confupdtype].as(:on_update),
594       co[:confdeltype].as(:on_delete),
595       cl2[:relname].as(:table),
596       rtable[:attname].as(:refcolumn),
597       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
598       nsp[:nspname].as(:schema)
599     ]}
600 
601   if reverse
602     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
603   end
604 
605   h = {}
606   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
607 
608   ds.each do |row|
609     if reverse
610       key = [row[:schema], row[:table], row[:name]]
611     else
612       key = row[:name]
613     end
614 
615     if r = h[key]
616       r[:columns] << m.call(row[:column])
617       r[:key] << m.call(row[:refcolumn])
618     else
619       entry = h[key] = {
620         :name=>m.call(row[:name]),
621         :columns=>[m.call(row[:column])],
622         :key=>[m.call(row[:refcolumn])],
623         :on_update=>fklod_map[row[:on_update]],
624         :on_delete=>fklod_map[row[:on_delete]],
625         :deferrable=>row[:deferrable],
626         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
627       }
628 
629       unless schema
630         # If not combining schema information into the :table entry
631         # include it as a separate entry.
632         entry[:schema] = m.call(row[:schema])
633       end
634     end
635   end
636 
637   h.values
638 end

    # File lib/sequel/adapters/shared/postgres.rb
640 def freeze
641   server_version
642   supports_prepared_transactions?
643   @conversion_procs.freeze
644   super
645 end

    # File lib/sequel/adapters/shared/postgres.rb
648 def indexes(table, opts=OPTS)
649   m = output_identifier_meth
650   oid = regclass_oid(table, opts)
651 
652   if server_version >= 90500
653     order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
654   else
655     range = 0...32
656     order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
657   end
658 
659   attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
660 
661   ds = metadata_dataset.
662     from{pg_class.as(:tab)}.
663     join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
664     join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
665     join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
666     left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
667     where{{
668       indc[:relkind]=>'i',
669       ind[:indisprimary]=>false,
670       :indexprs=>nil,
671       :indisvalid=>true,
672       tab[:oid]=>oid}}.
673     order(*order).
674     select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
675 
676   ds = ds.where(:indpred=>nil) unless opts[:include_partial]
677   ds = ds.where(:indisready=>true) if server_version >= 80300
678   ds = ds.where(:indislive=>true) if server_version >= 90300
679 
680   indexes = {}
681   ds.each do |r|
682     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
683     i[:columns] << m.call(r[:column])
684   end
685   indexes
686 end

    # File lib/sequel/adapters/shared/postgres.rb
689 def locks
690   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
691 end

    # File lib/sequel/adapters/shared/postgres.rb
699 def notify(channel, opts=OPTS)
700   sql = String.new
701   sql << "NOTIFY "
702   dataset.send(:identifier_append, sql, channel)
703   if payload = opts[:payload]
704     sql << ", "
705     dataset.literal_append(sql, payload.to_s)
706   end
707   execute_ddl(sql, opts)
708 end

    # File lib/sequel/adapters/shared/postgres.rb
711 def primary_key(table, opts=OPTS)
712   quoted_table = quote_schema_table(table)
713   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
714   sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}"
715   value = fetch(sql).single_value
716   Sequel.synchronize{@primary_keys[quoted_table] = value}
717 end

    # File lib/sequel/adapters/shared/postgres.rb
720 def primary_key_sequence(table, opts=OPTS)
721   quoted_table = quote_schema_table(table)
722   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
723   sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
724   if pks = fetch(sql).single_record
725     value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
726     Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
727   else
728     sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
729     if pks = fetch(sql).single_record
730       value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
731       Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
732     end
733   end
734 end

    # File lib/sequel/adapters/shared/postgres.rb
742 def refresh_view(name, opts=OPTS)
743   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
744 end

    # File lib/sequel/adapters/shared/postgres.rb
748 def reset_primary_key_sequence(table)
749   return unless seq = primary_key_sequence(table)
750   pk = SQL::Identifier.new(primary_key(table))
751   db = self
752   s, t = schema_and_table(table)
753   table = Sequel.qualify(s, t) if s
754 
755   if server_version >= 100000
756     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
757     increment_by = :seqincrement
758     min_value = :seqmin
759   else
760     seq_ds = metadata_dataset.from(LiteralString.new(seq))
761     increment_by = :increment_by
762     min_value = :min_value
763   end
764 
765   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
766 end

    # File lib/sequel/adapters/shared/postgres.rb
768 def rollback_prepared_transaction(transaction_id, opts=OPTS)
769   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
770 end

    # File lib/sequel/adapters/shared/postgres.rb
774 def serial_primary_key_options
775   auto_increment_key = server_version >= 100002 ? :identity : :serial
776   {:primary_key => true, auto_increment_key => true, :type=>Integer}
777 end

    # File lib/sequel/adapters/shared/postgres.rb
780 def server_version(server=nil)
781   return @server_version if @server_version
782   ds = dataset
783   ds = ds.server(server) if server
784   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
785 end

    # File lib/sequel/adapters/shared/postgres.rb
788 def supports_create_table_if_not_exists?
789   server_version >= 90100
790 end

    # File lib/sequel/adapters/shared/postgres.rb
793 def supports_deferrable_constraints?
794   server_version >= 90000
795 end

    # File lib/sequel/adapters/shared/postgres.rb
798 def supports_deferrable_foreign_key_constraints?
799   true
800 end

    # File lib/sequel/adapters/shared/postgres.rb
803 def supports_drop_table_if_exists?
804   true
805 end

    # File lib/sequel/adapters/shared/postgres.rb
808 def supports_partial_indexes?
809   true
810 end

    # File lib/sequel/adapters/shared/postgres.rb
819 def supports_prepared_transactions?
820   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
821   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
822 end

    # File lib/sequel/adapters/shared/postgres.rb
825 def supports_savepoints?
826   true
827 end

    # File lib/sequel/adapters/shared/postgres.rb
830 def supports_transaction_isolation_levels?
831   true
832 end

    # File lib/sequel/adapters/shared/postgres.rb
835 def supports_transactional_ddl?
836   true
837 end

    # File lib/sequel/adapters/shared/postgres.rb
813 def supports_trigger_conditions?
814   server_version >= 90000
815 end

    # File lib/sequel/adapters/shared/postgres.rb
848 def tables(opts=OPTS, &block)
849   pg_class_relname(['r', 'p'], opts, &block)
850 end

    # File lib/sequel/adapters/shared/postgres.rb
854 def type_supported?(type)
855   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
856   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
857   Sequel.synchronize{return @supported_types[type] = supported}
858 end

    # File lib/sequel/adapters/shared/postgres.rb
867 def values(v)
868   @default_dataset.clone(:values=>v)
869 end

    # File lib/sequel/adapters/shared/postgres.rb
879 def views(opts=OPTS)
880   relkind = opts[:materialized] ? 'm' : 'v'
881   pg_class_relname(relkind, opts)
882 end