PostgreSQL support for batched export

Just finished confirming that the non-batched export works on postgreSQL. I was able to export both csv and xls of 8,000 lines + with no problem. However, a batched export would be ideal for these situations.

Progress on Batched Export:
I had hoped that this would be as simple as adding an if statement that would use CREATE TABLE {tablename} AS... instead of CREATE TABLE {tablename} SELECT... when the database driver is pgsql. However, even after making that change, I still get a number of errors and an empty file (except for header). Next it is complaining about the @row := @row + 1 AS vde_weight bit of the statement... However, I have never seen such notation before (being completely unfamiliar with MySQL).

If anyone can explain this notation to me so that I can determine a postgreSQL equivalent it would help a Lot!

~Lacey

Okay, I've fixed the problem :) That said, I don't use git and can't seem to even clone this project let alone create a patch for it *Sighs!*

I'm using Debian 6.0; Git 1.7.2.5
I'm trying to run git clone --branch 6.x-2.0-beta5 http://git.drupal.org/project/views_data_export.git but I get an empty directory... Any idea what I'm doing wrong?

Anyway, Changes that need to be made: in plugins/views_data_export_plugin_display_export.inc Line 547 gets changed to

if ($this->_get_database_driver() == 'pgsql') {
  // Create temporary sequence
  $seq_name = $this->index_tablename() . '_seq';
  $create_seq_query = 'CREATE TEMP sequence ' . $seq_name;
  // query uses sequence to create row number
  $insert_query = 'CREATE TABLE {' . $this->index_tablename() . "} AS SELECT nextval('".$seq_name."') AS " . $this->batched_execution_state->sandbox['weight_field_alias'] . ', ' . implode(', ', $select_aliases) . ' FROM (' . $query . ') AS cl';
} else {
  // Original insert query remains unchanged
  $insert_query = 'CREATE TABLE {' . $this->index_tablename() . '} SELECT @row := @row + 1 AS ' . $this->batched_execution_state->sandbox['weight_field_alias'] . ', ' . implode(', ', $select_aliases) . ' FROM (' . $query . ') AS cl, (SELECT @row := 0) AS r';
}

The following is added directly above Line 555

      if ($this->_get_database_driver() == 'pgsql') {
        db_query($create_seq_query);
      }

And the incompatible pgsql driver gets removed from the array at Line 781.

Figured it out! git clone --branch 6.x-2.x http://git.drupal.org/project/views_data_export.git

Anyway I've attached a patch.

Testing:
This patch was tested on a Drupal 6.22 site with a postgresql 8.4.7 backend. First I needed a node view to test it on. I enabled the default frontpage view and changed the style of the Default display to table. I also added the title, teaser, post date and type fields. The view worked properly. I then added a Data Display of style csv with batched export set to yes. I also attached the data export display to the page display. When I click on the csv link on the frontpage page, I was brought to a progress bar which completely within 3 minutes and presented me with a csv file with 34,783 lines which corresponded to the number of rows in my view :) There were no errors displayed during this process. The index table was created properly.

As for justification of my changes:
Since the problem was the CREATE TABLE {tablename} SELECT ... which was causing problems for postgresql backends, I added an if statement to use a postgresql specific query if the driver used was pgsql and otherwise to use the original insert query. The postgresql specific query uses the postgresql specific CREATE TABLE {tablename} AS SELECT ....

Another postgresql incompatible piece of the query was the @row := @row + 1 which I interpreted to add a sequential row number to the table. The recommended way to do this in postgresql (compatible with earlier versions, easy to understand and read, better performance: see http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-...) is by creating a temporary sequence (will be dropped at end of session) and using that to create the row number -hence the next_val('sequencename'). I used the name of the table concatenated to _seq in order to ensure that the temporary sequence was unique and would not cause collisions.