Create or replace table dummy@inmemorystorage from execute native

Questions
,
1

We have various cases where being able to create a table from an execute native statement would greatly speed up processing.

This topic was discussed in Faster loading from execute native.

Example :

We are trying to synchronize with incremental approach some table in PostgreSQL. To do such we are leveraging a datetime_modified column. But we also need to know which records were deleted in the source container. In order to achieve this we need to execute server side :

execute native
''
|| ' SELECT all_ids AS missing_ids'
|| ' FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT '
|| 'MAX(id) FROM my_table)) all_ids'
|| 'EXCEPT SELECT id FROM my_table'
|| 'ORDER BY missing_ids ASC'
datacontainer 'PotsGres'

This statement returns 410K records on our dataset. We see from debug view (using trace) that the missing_ids are pulled quite quickly (15 seconds).

Then we need to manipulate this inside Invantive Query Tool. But inserting the values from a loop takes a very long time, more than 10 minutes:

create or replace table missing_ids@inmemorystorage
( id int
);
--
-- Takes 10 minutes.
--
begin
  for r in (execute native
              'SELECT all_ids AS missing_ids
              FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT MAX(id) FROM my_table)) all_ids
              EXCEPT SELECT id FROM my_table
              ORDER BY missing_ids ASC
              '
              datacontainer 'postgres')
  loop
    insert into missing_ids@inmemorystorage (id) values (r.missing_ids);
  end loop;
end;

It would be very useful to:

create or replace table my_ids@inmemorystorage 
as 
execute native
''
|| ' SELECT all_ids AS missing_ids'
|| ' FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT '
|| 'MAX(id) FROM my_table)) all_ids'
|| 'EXCEPT SELECT id FROM my_table'
|| 'ORDER BY missing_ids ASC'
              datacontainer 'PotsGres'

Approaches described in the faster-loading-from-execute-native like creating a view in postgres would work but give poor flexibility when update of the statement are needed.

2

There is an existing for this on:

The idea will be discussed in the course of this week for viability and then updated.