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: