title: Load contents from a CSV file into a PostgreSQL table
body: <p>Below is a description of the procedure I went through to try and load data from a file into a PostgreSQL 8.0 database running on a Linux RedHat 7.2 host.</p>
  
  <p>Now, my issue is that the <code>FOR EVERY ROW</code> trigger is getting called and the procedure is executing.</p>
  
  <p>What I'd like it to do, however, is have it check the appropriate row of my table once I have given in the filename and decide based on the contents of the record whether to do a <code>DUMP BULK DATA</code> or a <code>DUMP WHOLE CSV FILE</code> only once (on the trigger).</p>
  
  <p>Please help me solve this issue...</p>
  
  <p>My <code>logfile.tmp</code> is as follows:</p>
  
  <pre><code>27/Apr/2013:17:03:42 +0530#192.168.1.3#16#0@#$http://localhost/images/
     banner-left.jpg@#$10.1ff.ff.ff#-#Y#-
  27/Apr/2013:17:03:42 +0530#192.168.1.3#16#0@#$http://localhost/images/
     banner-left.jpg@#$10.ff.ff.2ff05#-#Y#-
  </code></pre>
  
  <p>The COPY command I am using:</p>
  
  <pre><code>/usr/local/pgsql/bin/psql localhost -d d1 -U u1 -tc "COPY tblaccesslog ( accesstime, clientip, username, request,bytes, urlpath, url, contenttype, issite, webcatname)  FROM 'logfile.tmp' WITH DELIMITER AS '#';" &gt;&gt; /tmp/parselog.log 2&gt;&amp;1
  </code></pre>
  
  <p>The trigger (<code>insert_accesslog_trigger</code>) in question:</p>
  
  <pre><code>insert_accesslog_trigger BEFORE INSERT ON tblaccesslog FOR EACH ROW EXECUTE PROCEDURE accesslog_insert_trigger()
  </code></pre>
  
  <p>and finally the trigger function (<code>accesslog_insert_trigger()</code>) being used:</p>
  
  <pre><code>accesslog_insert_trigger()
  DECLARE
          tablemaxtuples NUMERIC(10);
          tableno NUMERIC(10);
          newtable TEXT;
          query TEXT;
          tablecount NUMERIC(10);
          min_limit NUMERIC(10);
          max_limit NUMERIC(10);
  BEGIN
  
          tablemaxtuples := 100000;
      tableno := ( NEW.id - ( NEW.id % tablemaxtuples ) ) / tablemaxtuples +1;
      newtable := 'tblaccesslog'||to_char(CURRENT_DATE,'YYYYMMDD')||'_child_'||tableno;
  
          SELECT trim(count(tablename)) INTO tablecount FROM pg_tables WHERE tablename=newtable ;
      IF tablecount = 0
      THEN
                  min_limit := (tableno-1)*tablemaxtuples;
                  max_limit := min_limit + tablemaxtuples;
                                  query := 'CREATE TABLE '||newtable||'( PRIMARY KEY (id),CHECK ( id &gt;= '||min_limit||' AND id &lt;'||max_limit||'  ) ) INHERITS (tblaccesslog)';
          EXECUTE query;
       END IF;
  
      query := 'INSERT INTO '|| newtable ||' ( id, username, clientip, url, accesstime, requestbytes, contenttype, issite, urlpath, webcatname ) VALUES ('||NEW.id||','''||NEW.username||''','''||NEW.clientip||''','''||NEW.url||''','''||NEW.accesstime||''','''||NEW.requestbytes||''','''||NEW.contenttype||''','''||NEW.issite||''','''|| replace(NEW.urlpath,'\'','') ||''','''||NEW.webcatname||''')';
      EXECUTE query;
      RETURN NULL;
  
  END;
  </code></pre>