question , about join

[cooltnt]

Want to ask for advice.

Whether it is possible to directly join the table when creating an foreign table, not create two foreign table first then join

[surajkharage19]

Hi @cooltnt,

We don't have such functionality, you need to create foreign tables separately to join.

[cooltnt]

Hi @cooltnt,

We don't have such functionality, you need to create foreign tables separately to join.

ok , but I think if can use query when create foreign table, that would be great.

Because many times I just want the results of multiple table joins.

And I found that I could not get the view in MySQL, Is this operation not supported ?

[surajkharage19]

Hi @cooltnt,

ok , but I think if can use query when create foreign table, that would be great.

Currently, this is not on our roadmap and it seems we don't have infrastructure for the same.

And I found that I could not get the view in MySQL, Is this operation not supported ?

You can create a foreign table on the remote MySQL view. You can perform select operations on a foreign table but can NOT do insert/update/delete because of the unique column requirement.

Hope this helps.

[cooltnt]

Hi @cooltnt,

ok , but I think if can use query when create foreign table, that would be great.

Currently, this is not on our roadmap and it seems we don't have infrastructure for the same.

And I found that I could not get the view in MySQL, Is this operation not supported ?

You can create a foreign table on the remote MySQL view. You can perform select operations on a foreign table but can NOT do insert/update/delete because of the unique column requirement.

Hope this helps.

Hi @surajkharage19 ,

I tried to create a view on remote MySQL, but an error occurred when select the foreign table ( The view in MySQL is normal )

"ERROR: failed to prepare the MySQL query: Table 'f_eosbdata.v_zt_ksxxh_old' doesn't exist"

create sql : CREATE FOREIGN TABLE public.v_zt_ksxxh_old_mysql_fdw_test( KMMC text NULL, KSMC text NULL, KSFL text NULL ) SERVER mysql_server_test OPTIONS (dbname 'f_eosbdata', table_name 'v_zt_ksxxh_old');

[surajkharage19]

Hi @cooltnt,

Can you please share the complete test case to check further? I have tested this at my end and it is working fine. Please refer below test case for your reference.

MySQL:
mysql> create view v_20 as select t1.id from t19 t1 inner join t20 t2 on (t1.id = t2.id);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> select * from v_20;
+-----+
| id  |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)
EPAS 14:
edb@77423=#CREATE FOREIGN TABLE f_v20 (id int)
  SERVER mysql_svr OPTIONS (dbname 'suraj', table_name 'v_20');
CREATE FOREIGN TABLE
edb@77423=#
edb@77423=#select * from f_v20;
 id
-----
 111
(1 row)

Please note that this is NOT SUPPORTED and well-tested functionality but can work in some cases

[cooltnt]

Hi @surajkharage19

I made a very stupid mistake, now it is working fine.

Thank you very much for your attention.