README.md in embulk-input-postgresql-0.9.2 vs README.md in embulk-input-postgresql-0.9.3
- old
+ new
@@ -22,10 +22,11 @@
- **ssl**: enables SSL. data will be encrypted but CA or certification will not be verified (boolean, default: false)
- **application_name**: application name shown on pg_stat_activity. (string, default: "embulk-input-postgresql")
- **options**: extra JDBC properties (hash, default: {})
- If you write SQL directly,
- **query**: SQL to run (string)
+ - **use_raw_query_with_incremental**: If true, you can write optimized query using prepared statement by yourself. See [Use incremental loading with raw query](#use-incremental-loading-with-raw-query) for more detail (boolean, default: false)
- If **query** is not set,
- **table**: destination table name (string, required)
- **select**: expression of select (e.g. `id, created_at`) (string, default: "*")
- **where**: WHERE condition to filter the rows (string, default: no-condition)
- **order_by**: expression of ORDER BY to sort rows (e.g. `created_at DESC, id ASC`) (string, default: not sorted)
@@ -112,9 +113,41 @@
CREATE INDEX embulk_incremental_loading_index ON table (updated_at, id);
```
Recommended usage is to leave `incremental_columns` unset and let this plugin automatically finds an auto-increment (serial / bigserial) primary key. Currently, only strings and integers are supported as incremental_columns.
+### Use incremental loading with raw query
+
+**IMPORTANT**: This is an advanced feature and assume you have an enough knowledge about incremental loading using Embulk and this plugin
+
+Normally, you can't write your own query for incremental loading.
+`use_raw_query_with_incremental` option allow you to write raw query for incremental loading. It might be well optimized and faster than SQL statement which is automatically generated by plugin.
+
+Prepared statement starts with `:` is available instead of fixed value.
+`last_record` value is necessary when you use this option.
+Please use prepared statement that is well distinguishable in SQL statement. Using too simple prepared statement like `:a` might cause SQL parse failure.
+
+In the following example, prepared statement `:foo_id` will be replaced with value "1" which is specified in `last_record`.
+
+```yaml
+in:
+ type: postgresql
+ query:
+ SELECT
+ foo.id as foo_id, bar.name
+ FROM
+ foo LEFT JOIN bar ON foo.id = bar.id
+ WHERE
+ foo.hoge IS NOT NULL
+ AND foo.id > :foo_id
+ ORDER BY
+ foo.id ASC
+ use_raw_query_with_incremental: true
+ incremental_columns:
+ - foo_id
+ incremental: true
+ last_record: [1]
+```
## Example
```yaml
in: