[teiid-issues] [JBoss JIRA] (TEIID-5397) Make RETURN_GENERATED_KEYS work with views

Make RETURN_GENERATED_KEYS work with views ------------------------------------------ Key: TEIID-5397 URL: https://issues.jboss.org/browse/TEIID-5397 Project: Teiid Issue Type: Feature Request Affects Versions: 10.2.1 Reporter: Lukáš Svačina Assignee: Steven Hawkins Fix For: 11.1 *+VDB:+* {code} <vdb name="_GENERATED_form_shoes" version="2"> <model name="_INTERNAL_internalModel" type="PHYSICAL"> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/> </model> <model name="GEN_view" type="VIRTUAL"> <metadata type="DDL"> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model", "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]> </metadata> </model> </vdb> {code} *+TABLE:+* name: form_shoes_2 columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*) *+PROBLEM:+* Connected into VDB using JDBC like: {code:java} final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS); statement.executeUpdate(); final ResultSet generatedKeys = statement.getGeneratedKeys(); {code} *generatedKeys is empty if:* # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK! # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here? *+QUESTIONS:+* # How to get last_insert_id() when inserting into foreign table/views (with/without joins)? # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows? Thanks for fixing/adding this functionality.