DZone Snippets: postgresql code

TSearch2 - Upgrade PostgreSQL 8.1 to 8.3

k1mmeh (Kim Chirnside) — Thu, 12 Jun 2008 02:34:44 GMT

I had terrible trouble getting my tsearch to work after migrating from 8.1 to 8.3. If you are getting errors like:

ERROR: text search configuration "default" does not exist

you need to create a "default" configuration.
Use the following to get a list of configurations:



postgres=# \dF

Now create a configuration copying it from the language configuration of your choice like so:



postgres=# CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english );

Ruby on Rails - PostgreSQL Foreign Keys

k1mmeh (Kim Chirnside) — Mon, 02 Jun 2008 10:11:59 GMT

To utilise foreign keys in my database while still using the super tidy Rails migrations, I created these migration helpers to CREATE and DROP foreign keys. NOTE: this was written for a PostgreSQL database so it will likely not work on other DBs.



  def foreign_key(from_table, from_column, to_table)

    constraint_name = "fk_#{from_table}_#{from_column}"

    

    execute %{ALTER TABLE #{from_table}

              ADD CONSTRAINT #{constraint_name}

              FOREIGN KEY (#{from_column})

              REFERENCES #{to_table}(id)}

  end

  

  def drop_foreign_key(from_table, from_column, to_table)

    constraint_name = "fk_#{from_table}_#{from_column}"

    

    execute %{ALTER TABLE #{from_table}

              DROP CONSTRAINT #{constraint_name}}

  end

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:43 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:19 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:08 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

Setting up Ruby On Rails with PostgreSQL on Mac OS X 10.4.9

ntk () — Thu, 21 Jun 2007 21:25:53 GMT

This is a modified version of the Ruby on Rails Tutorial (TutorialStepOne, TutorialStepOnePostgresql, ...).

Log in to an admin user account and, if necessary, fix your command search path in $HOME/.bash_profile, $HOME/.bash_login or $HOME/.profile to include "/usr/local" and "/usr/local/sbin" (cf. Using /usr/local > Set The Path; echo $PATH | tr ":" "\n"). If there are no such files, just create them: touch $HOME/.bash_login && touch $HOME/.bashrc (ls -a | grep \.bash).

To avoid RubyGems loading issues it's no bad idea to add export RUBYOPT=rubygems to your $HOME/.bash_login file as well.

If you want your system path changes to take effect not only for you you can modify the global system path settings in the systemwide initialization files /private/etc/profile and /private/etc/bashrc accordingly.

To fix the paths of installed manual pages add the lines "MANPATH /usr/local/share/man" and "MANPATH /usr/local/man" to sudo nano +45 /usr/share/misc/man.conf (man -w | tr ":" "\n").

(USE THE FOLLOWING AT YOUR OWN RISK!)

REQUIREMENTS:

I. Xcode

II. PostgreSQL Database Server

Install this PostgreSQL Database Server package.



# test after installation

which psql     # /usr/local/bin/psql

psql --version     # psql (PostgreSQL) 8.2.3, contains support for command-line editing

Alternative installation: Getting PostgreSQL running for Rails on a Mac

III. Ruby 1.8.6, Ruby On Rails 1.2.3 & Mongrel 1.0.1

Building Ruby, Rails, Subversion, Mongrel, and MySQL on Mac OS X

To install both Mongrel & Mongrel Cluster use: sudo gem install -y mongrel mongrel_cluster (cf. Using Mongrel Cluster).



# test after installation

ruby -v     # ruby 1.8.6

rails -v     # Rails 1.2.3

gem list     # ... mongrel (1.0.1) ...

IV. ruby-postgres 0.7.1



sudo gem install -y ruby-postgres

As an alternative you may try Ruby on rails installer script for Mac OSX or choose to install via MacPorts as described in Installing Ruby on Rails and PostgreSQL on OS X, Second Edition or Installing Rails on Mac OS X Tiger (10.4.8). However, you then may have to change your system paths mentioned above accordingly.

1. create a database server





# first create a directory called PostgreSQL-db on your Desktop

mkdir -p $HOME/Desktop/PostgreSQL-db



# create a new db server called railsdb

/usr/local/bin/initdb -E UTF8 -D $HOME/Desktop/PostgreSQL-db/railsdb



# START DB SERVER

dir="$HOME/Desktop/PostgreSQL-db"; /usr/local/bin/pg_ctl -D $dir/railsdb -l $dir/railsdb/postgres.log start



# STOP DB SERVER

#dir="$HOME/Desktop/PostgreSQL-db"; /usr/local/bin/pg_ctl -D $dir/railsdb -l $dir/railsdb/postgres.log stop -m smart



# check

cat $HOME/Desktop/PostgreSQL-db/railsdb/pg_hba.conf



   ...

   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD



   # "local" is for Unix domain socket connections only

   local   all         all                               trust

   # IPv4 local connections:

   host    all         all         127.0.0.1/32          trust

   # IPv6 local connections:

   host    all         all         ::1/128               trust

2. create PostgreSQL database





createdb `whoami`_development

#dropdb `whoami`_development



createdb `whoami`_test

#dropdb `whoami`_test



#createdb `whoami`_production

#dropdb `whoami`_production

3. set up your Rails project





cd $HOME/Desktop/RubyOnRails-projects

rails -d postgresql `whoami`

cd `whoami`

4. edit /config/database.yml





open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/config/database.yml



# just uncomment the following line

#encoding: UTF8

5. make Rails & PostgreSQL work together





cd $HOME/Desktop/RubyOnRails-projects/`whoami`



ruby script/generate migration People



# edit /db/migrate/001_people.rb

# cf. http://wiki.rubyonrails.org/rails/pages/TutorialStepOneMigrations



open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/db/migrate/001_people.rb



 class People < ActiveRecord::Migration



    def self.up

      create_table :people do |table|

      # note that "id" is added implicitly, by default

        table.column :name, :string

        table.column :street1, :string

        table.column :street2, :string

        table.column :city, :string

        table.column :state, :string

        table.column :zip, :string

     end

    end



    def self.down

      drop_table :people 

    end

  end







rake db:migrate



ruby script/generate model Person  



#open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/app/models/person.rb  # file will be explained below



ruby script/console



>> ...

        entry = Person.new

        entry.name = "Name" 

        entry.street1 = "123 Somwhere" 

        entry.street2 = "" 

        entry.city = "Smallville" 

        entry.state = "KS" 

        entry.zip = "123456" 

        entry.save

        exit





# check newly created db table



psql `whoami`_development

SELECT * FROM people;

\q





# test

rake      # ... 0 failures, 0 errors



# create new controller

ruby script/generate controller People list view new edit





# edit /app/controllers/people_controller.rb

open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/app/controllers/people_controller.rb



  def view

   @person = Person.find(1)

  end







# edit /app/views/people/view.rhtml

open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/app/views/people/view.rhtml



# copy & paste & uncomment the following lines



#

#  

#    People#view

#    This page will display one person.

#    

#    <%= @person.name %>


#    <%= @person.street1 %>


#    <%= @person.street2 %>


#    <%= @person.city %>


#    <%= @person.state %>


#    <%= @person.zip %>


#    

#  

#





# the file /app/models/person.rb explained (see above)

# open -e $HOME/Desktop/RubyOnRails-projects/`whoami`/app/models/person.rb



# class Person < ActiveRecord::Base

# end



# How does this know to map to the people table we created? ActiveRecord pluralizes the class name and looks for that 

# table in the database. This doesn’t just mean adding an ’s’. Irregular plural forms are also handled, so Rails knows 

# that the plural of ‘person’ is ‘people’. The rules for how it does this are described in the documentation

# (see http://wiki.rubyonrails.org/rails/pages/TutorialStepSix).



# test

rake



# start your Rails app

cd $HOME/Desktop/RubyOnRails-projects/`whoami`



ruby script/server

#ruby script/server --environment=development



# open a second shell window and go to ...  

open -a Safari http://localhost:3000/people/view

PostgreSQL: generate DDL to alter views with dependencies

dseverin () — Thu, 17 May 2007 14:19:15 GMT

Determine dependencies on given table/view or their columns, types (base/domain/composite), functions, rules and show DROP/ALTER/CREATE series to update their definitions.



#!/usr/bin/ruby -W0

#

# This script tries to determine all dependencies on given table/view or their columns, 

# types (base/domain/composite), functions, rules.

#

# Usage:

#       ruby show_obj_deps conn_string object_id alter_stmt 

# e.g:

#       ruby show_obj_deps dbname=db1 "VIEW public.base_stats" "DROP VIEW public.base_stats; CREATE VIEW public.base_stats AS SELECT ..."

#

# Result will be series of DDL DROP statements for dependent objects, then alter_stmt, and

# then series of DDL CREATE statements for dropped objects.

#

# Format of object_id:

#   TABLE schema_name.table_name

#   VIEW schema_name.view_name

#   TABLE schema_name.table_name COLUMN column_name

#   VIEW schema_name.view_name COLUMN column_name

#   FUNCTION schema_name.func_name(type_1, type2, ...)

#   RULE rule_name ON schema_name.obj_name

#

# This script is somewhat rewritten version of http://snippets.dzone.com/posts/show/2105

#

# Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding

# 



require 'postgres'

require 'tsort'

require 'pp'

PGconn.translate_results = true



$PG_CLASSES = {}

class PgDependencyGraph            

class DBObject

  attr_accessor :row, :o_type, :nsp

  def initialize(conn, class_id, obj_id, sub_id)

    @o_type   = $PG_CLASSES[class_id]

    @row   = conn.query(sql_for(@o_type, obj_id, sub_id)).first

    @nsp = row['nsp']

    if @o_type == 'pg_proc'

      arg_types = row.last.split(" ")

      unless arg_types.empty?

        arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")

        row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"

      else 

	     row[-1] = "()"

      end

    end

  end

  def sql_for(pg_class, obj_id, sub_id)

  base_sql = case pg_class 

    when 'pg_type' 

      "select       (case 

      	when typtype = 'b' then 'BASE '

	when typtype = 'c' then 'COMPOSITE '

	when typtype = 'd' then 'DOMAIN '

	when typtype = 'p' then 'PSEUDO '

      end) || coalesce( 

      (select (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

	    WHEN relkind = 'c' THEN ' '

        END) from pg_class c where c.oid = typrelid), ' '), 

(select nspname from pg_namespace n where n.oid = typnamespace) as nsp,       typname as obj_name,

      #{sub_id} from pg_type  "

    when 'pg_proc' 

      "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "

    when 'pg_class'

      "select 

  (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

        END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp, 

       relname as obj_name,  

      (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"

    when 'pg_rewrite'

      "select (select nspname from pg_namespace n where n.oid = (select relnamespace from pg_class c where c.oid = ev_class) ) as nsp, rulename, (select relname from pg_class c where c.oid = ev_class)  from pg_rewrite"

    else

        puts "IGNORE: #{pg_class}, #{obj_id}, #{sub_id}"

        nil

  end

  base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil

  

end

 

  def to_s

    case @o_type

    	when 'pg_proc' : "FUNCTION #{@row[0]}.#{@row[1]}#{@row[2]}"

      when 'pg_type': "TYPE: #{@row[0]} #{@row[1]}.#{@row[2]}"

      when 'pg_class' : "#{@row[0]} #{@row[1]}.#{@row[2]}" + (row[3] ? " COLUMN #{@row[3]}" : "")

      when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW #{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON #{@row[0]}.#{@row[2]}"

    end 

  end

end

  attr_accessor :depend_graph

    ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']

  def initialize(conn)

    conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|

      $PG_CLASSES[row['classid']] = row['relname']

    end

    conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|

      $PG_CLASSES[row['refclassid']] = row['relname']

    end

    dep_graphs = {}

    conn.query("SELECT * FROM pg_catalog.pg_depend   where  true or ( deptype<> 'i' and deptype <> 'p')").each do |row|

    if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])

      this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])

      that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])

      #dep_string = this_obj.nsp !~ /^pg_toast$/ ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil

      dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/  ? this_obj.to_s : nil

    

      if dep_string && this_obj.to_s !~ /^INDEX /

        dep_graphs[that_obj.to_s] ||=[]

        dep_graphs[that_obj.to_s] << dep_string

        if that_obj.to_s =~ /^(VIEW|TABLE) (.+?) COLUMN /m

          ds2 = that_obj.to_s.sub(/ COLUMN.*/m, '')

          dep_graphs[ds2] ||=[]

          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2

        elsif that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW) /

          ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW) /, '\1 ')

          dep_graphs[ds2] ||=[]

          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2

        end

      end

    end

  end

  dep_graphs.each do |that, values|

    values.uniq!

    values.reject! {|item| item == that}

  end

  @depend_graph = dep_graphs

 end

 def list_dependencies(obj)

    dep_list = []

    if @depend_graph[obj]

      @depend_graph[obj].each do |v|

        k = list_dependencies(v)

        k.empty? ? dep_list << v : dep_list << [v, k]

      end

    end

    dep_list

 end

end



class DG

 include TSort

 def initialize(dep_graph, node_list)

   @nodes = node_list

   @dg = dep_graph

 end

 def tsort_each_node(&block)

   @nodes.each {|x| yield x}

 end

 def tsort_each_child(node, &block)

   (@dg[node]||[]).each(&block)

 end

end    



class Function

  attr_reader :typed_head

  def initialize(conn, tuple)

    @name = tuple['namespace'] + "." + tuple['function_name']

    @language = tuple['language_name']

    @src = tuple['source_code']

    @returns_set = tuple['returns_set']

    @return_type = format_type(conn, tuple['return_type'])

    @tipes = tuple['function_args'].split(" ")

    if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/

      @arnames = $1.split(',')

    elsif tuple['function_arg_names'].is_a? Array

      @arnames = tuple['function_arg_names']

    else

      @arnames = [""] * @tipes.length

    end

    alist = []

    atypelist = [] 

    @tipes.each_with_index do |typ,idx|

      ft = format_type(conn, typ)

      alist << (@arnames[idx] +" " + ft)

      atypelist << ft

    end

    @arglist = alist.join(" , ")

    @strict = tuple['proisstrict'] ? ' STRICT' : ''

    @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : ''

    @volatile = case tuple['provolatile']

      when 'i' then ' IMMUTABLE'

      when 's' then ' STABLE'

      else ''

    end

    @typed_head = @name+"("+atypelist.join(", ")+")"

  end

  def signature

    "#{@name}(#{@arglist})"

  end

  def definition

    <<-EOT

CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ?  'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef};

EOT

  end

  def == (other)

    definition == other.definition

  end

  def format_type(conn, oid)

    t_query = <<-EOT

    SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name

     FROM pg_catalog.pg_type

     JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace)

     WHERE pg_type.oid = 

    EOT

    return conn.query(t_query + oid.to_s)[0][0]

  end

  def self.find(conn, schema, name, args)

    func_query = <<-EOT

     SELECT proname AS function_name

     , nspname AS namespace

     , lanname AS language_name

     , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment

     , proargtypes AS function_args

     , proargnames AS function_arg_names

     , prosrc AS source_code

     , proretset AS returns_set

     , prorettype AS return_type,

     provolatile, proisstrict, prosecdef

     FROM pg_catalog.pg_proc

     JOIN pg_catalog.pg_language ON (pg_language.oid = prolang)

     JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid)

     JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid)

     WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema|pg_temp_'

     AND nspname = $1

     AND proname = $2

     AND oidvectortypes(proargtypes) = $3

    EOT



    Function.new(conn, conn.query(func_query, schema, name, args).first)

  end

end



conn = PGconn.new(ARGV[0])

graph = PgDependencyGraph.new(conn)

#pp graph.list_dependencies(ARGV[1])

dep_list = []

ARGV[1].split(/\|/).each do |obj_id|

  dep_list += graph.list_dependencies(obj_id).flatten.uniq

end

dep_list.uniq!



top_sorted = DG.new(graph.depend_graph, dep_list).tsort

top_sorted.each do |line|

  case line

        when /^TYPE:/, /^VIEW (\S+) COLUMN/; 

        when /^VIEW (.+)/ then puts "DROP VIEW #$1;"

        when /^FUNCTION (.+)/ then puts "DROP FUNCTION #$1;"

        when /^RULE / then puts "DROP "+line+";"

        else 

        puts "-- SKIP #{line}"

  end

end



puts "", "--- ", "--- ALTER: ", "---"

puts ARGV[2]

puts "---", "---", ""

def view_def(conn, name)

  conn.select_value("SELECT pg_catalog.pg_get_viewdef('#{name}'::regclass, true)")

end

def rule_def(conn, tablename, rule_name)

 conn.select_value("select definition from pg_rules  where schemaname || '.' ||  tablename = $1  and rulename = $2", tablename, rule_name)

end



def func_def(conn, *args)

 Function.find(conn, *args).definition

end





top_sorted.reverse.each do |line|

  case line

        when /^TYPE:/, /^VIEW (\S+) COLUMN/ then puts "-- SKIP #{line}"

        when /^VIEW (.+)/ then puts "CREATE VIEW #$1 AS "+view_def(conn, $1)

        when /^FUNCTION (\w+)\.(\w+)\((.*)\)/ then puts func_def(conn, $1, $2, $3)

        when /^RULE (.+) ON (.+)/ then puts "CREATE "+rule_def(conn, $2, $1)

        else

          puts "-- SKIP #{line}"

  end

  puts 

end

Dump postgres production data into development database

nicwilliams (Dr Nic Williams) — Sun, 06 May 2007 09:00:17 GMT

When bug requests come in, its great to grab a copy of the current production (or system test env) data and sync it into your development database.

Here's a capistrano recipe for postgresql:



desc "Dumps target database into development db"

task :sync_db do

  env   = ENV['RAILS_ENV'] || ENV['DB'] || 'production'

  file  = "#{application}.sql.bz2"

  remote_file = "#{shared}/log/#{file}"

  run "pg_dump --clean --no-owner --no-privileges -U#{db_user} -h#{db_host} #{db_name}_#{env} | bzip2 > #{file}" do |ch, stream, out|

    ch.send_data "#{db_password}\n" if out =~ /^Password:/

    puts out

  end

  puts rsync = "rsync #{user}@#{domain}:#{file} tmp"

  `#{rsync}`

  puts depackage = "bzcat tmp/#{file} | psql #{local_db_dev}"

  `#{depackage}`

end

Populate PostgreSQL tables

ribafs (Ribamar FS) — Fri, 30 Mar 2007 15:06:16 GMT

This script in PHP populate with demo data tables Postgresql.
It insert serious registers automatically in a table.
Remove the primary key before run.



/** Rotina para popular tabela do PostgreSQL com massa de testes

  * Colaboração de Ribamar FS - http://ribafs.net - 14/03/2007

  * Requer remoção da chave primáia antes da execução.

  */ 

?>



Inserir Registros de Teste



Cadastrar Tabela do PostgreSQL com Massa de Testes

Remova chave primáia da tabela, antes de executar

Observe que nem todos os tipos de dados foram contemplados, alguns ficarão como string

















Host 
Banco 
Usuário 
Senha 
Tabela 
Registros 










if(isset($_POST['popular'])){

	$host=$_POST['host'];

	$banco=$_POST['banco'];

	$usuario=$_POST['usuario'];

	$senha=$_POST['senha'];

	$tabela=$_POST['tabela'];

	$registros=$_POST['registros'];



	$conexao=pg_connect("host=$host user=$usuario password=$senha dbname=$banco port=5432");

	if (!$conexao){

		die('Erro ao conectar ao banco
'.pg_last_error($conexao));

	}

	$str="SELECT * FROM $tabela";

	$consulta= pg_query($conexao,$str);

	$nc=pg_num_fields($consulta);

	$nr=pg_num_rows($consulta);



   $n='';//numericos (int, tinyint, smallint, bigint, etc)

   $r=''; //reais (float e double)

   $s="'";//strings

   $d=date('Y-m-d'); //datas

   $dt=date('Y-m-d H:i:s');//datatimes

   $o=''; //outros



   	$inscod .="INSERT INTO $tabela (";

   	for ($j = 0; $j < $nc; $j++) {

      $campo = pg_field_name($consulta, $j);

		if ($j < $nc-1)	$inscod .= "$campo,";

     	else $inscod .= "$campo";

	}

	$inscod .= ")";	 

    $inscod .= " VALUES (";



   for ($j = 0; $j < $nc; $j++) {   

   $tam   = pg_field_size($consulta, $j);

   if($tam == -1) $tam=20; //Caso queira limitar os campos ao máximo de 20 posiçes

		if ($j < $nc-1){ 

			switch (pg_field_type($consulta, $j)){

				case 'int4':

					$n=str_pad($n,$tam,'12345679890');

					$inscod .= "$n,";

					break;

     			case 'float4':

					$r=str_pad($r,$tam,'1234567890');

					$inscod .= "$r,";

					break;

				case 'bpchar':

				case 'varchar':

					$s=str_pad($s,$tam,"abcdefghijklmnopqrstuvxyz");

					$inscod .= "$s',";

					break;

				case 'date':

		 		    $inscod .= "'$d',";

					break;

				case 'timestamp':

					$inscod .= "'$dt',";

					break;

				case 'text':

					$inscod .= "'$t'";

					break;

				default:

					$o=str_pad($o,$tam,"abcdefghijklmnopqrstuvxyz");

					$inscod .= "'$o',";

					break;

			}

     	}else{

     		switch (pg_field_type($consulta, $j)){

				case 'int4':

					$n=str_pad($n,$tam,'1234567890');

					$inscod .= "$n";

					break;

     			case 'float4':

					$r=str_pad($r,$tam,'1234567890');

					$inscod .= "$r";

					break;

				case 'bpchar':

				case 'varchar':

					$s=str_pad($s,$tam,"abcdefghijklmnopqrstuvxyz");

					$inscod .= "$s'";

					break;

				case 'date':

					$inscod .= "'$d'";

					break;

				case 'timestamp':

					$inscod .= "'$dt'";

					break;

				case 'text':

					$inscod .= "'$t'";

					break;

				default:

					$o=str_pad($o,$tam,"abcdefghijklmnopqrstuvxyz");

					$inscod .= "'$o'";

					break;

     		}

     	}

     	

	}

    $inscod .=");";	

	

    for($r=1;$r<=$registros;$r++){	   

		//echo  $inscod;

	   	if(!pg_query($conexao,$inscod)) die ("Erro na inclusã
".pg_last_error($conexao));

    }	   

}

?>

Abilitazione accesso remoto per PostgreSQL

morlandi (Mario Orlandi) — Fri, 22 Dec 2006 15:51:26 GMT

// in "pg_hba.conf"



host    all         all         192.168.98.0/24       md5

// in "postgresql.conf"



listen_addresses = '*'		# what IP address(es) to listen on;