Implement some way to show the sql used to create a view

[alamb]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

As suggested by @Dandandan on #2279 (comment) it would be very nice to be able to see the original SQL used to define a view.

Apparently postgres uses a function: https://stackoverflow.com/questions/14634322/how-to-see-the-create-view-code-for-a-view-in-postgresql

Mysql uses SHOW CREATE VIEW: https://dev.mysql.com/doc/refman/8.0/en/show-create-view.html

mysql> create view f as select * from foo;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view f;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                        | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| f    | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `f` AS select `foo`.`bar` AS `bar`,`foo`.`baz` AS `baz` from `foo` | utf8mb4              | utf8mb4_0900_ai_ci   |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Describe the solution you'd like
Store the original SQL and make it accessable via SQL

Describe alternatives you've considered

Additional context

#2279

[comphead]

@alamb when we talk about view metadata, its supposed we dont have a persistent metadata catalog? Like Hive or similar.

So its expected the view is temporary, like in Spark?

[alamb]

@comphead -- I think the idea is that the View definition is stored in whatever implements TableProvider (aka ViewTable in https://github.com/apache/arrow-datafusion/pull/2279/files). In order to show the original SQL somehow that information would have to be passed out (perhaps via TableProvider or some other trait)

[mrob95]

I'll have a go at this

[alamb]

Thank you very much @mrob95 -- we have appreciated all your other fine work to date as well!