Re: Materialized Views - Mailing list pgsql-odbc
| From | Jean-Marc Guazzo |
|---|---|
| Subject | Re: Materialized Views |
| Date | |
| Msg-id | CAJ3aXhoHD2=Tnz3VMvseegYAgYJOO6oGWObLAbC8EJh=jTOykw@mail.gmail.com Whole thread Raw |
| In response to | Re: Materialized Views (Jean-Marc Guazzo <jmguazzo@gmail.com>) |
| Responses |
Re: Materialized Views
|
| List | pgsql-odbc |
Hi,
Same test with Oracle Express 11g:
create table login_test.test1 (id int primary key, contenu varchar(100));
create materialized view login_test.test_vm as select * from login_test.test1;
MSQRY32 192c-1b0c ENTER SQLTablesW
HSTMT 0x00C6CBC0
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00C6DDE0 [ 24] "'TABLE','VIEW','SYNONYM'"
SWORD 24
For DB2, I found this :
Valid table type identifiers can include: TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM, GLOBAL TEMPORARY TABLE, AUXILIARY TABLE, MATERIALIZED QUERY TABLE, or ACCEL-ONLY TABLE.
On a more rethorical pov,what's the difference between a materialized view and a view for the end user behind ODBC ? Should there be one ?
JM.
Le jeu. 23 juil. 2015 à 12:05, Jean-Marc Guazzo <jmguazzo@gmail.com> a écrit :
Well, I made a test by creating a 'indexed view' in sql server which is supposed to be the same as a materialized view. (http://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server)On a SQL server DB, I created the following itemscreate table sample_table( id int identity primary key, value varchar(100));create view sample_ix_view WITH SCHEMABINDING as select id,value from dbo.sample_table;create unique clustered index ix_sample_ix_view on dbo.sample_ix_view(id);I activated ODBC trace log and opened it with Access.Excerpt from this trace :MSACCESS 2728-410 ENTER SQLTablesW...WCHAR * 0x67DFBBA0 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0"...MSACCESS 2728-410 EXIT SQLTablesW with return code 0 (SQL_SUCCESS)...WCHAR * 0x67DFBBA0 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0"...MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)...PTR 0x00717C34 [ 24] "sample_table"...MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)...PTR 0x00717B28 [ 10] "TABLE"...MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)...PTR 0x00717C34 [ 28] "sample_ix_view"...MSACCESS 2728-410 EXIT SQLGetData with return code 0 (SQL_SUCCESS)...PTR 0x00717B28 [ 8] "VIEW"...JM.Le jeu. 23 juil. 2015 à 03:16, Heikki Linnakangas <hlinnaka@iki.fi> a écrit :On 07/23/2015 05:37 AM, Michael Paquier wrote:
> On Thu, Jul 23, 2015 at 11:05 AM, Jean-Marc Guazzo <jmguazzo@gmail.com> wrote:
>> Materialized Views aren't visible when I try to link them with MS Access, MS
>> Excel or LibreOffice Base.
>
> There is the same problem with foreign tables actually.
>
>> I guess that's because the ODBC driver doesn't return this information...
>>
>> Can you tell me whether there will be some adjustement in the next version
>> of the odbc driver regarding the MVs ?
>
> Hard to say... The following patch is not loved enough I am afraid:
> http://www.postgresql.org/message-id/CAB7nPqR0apHpiPAi4J2e3oR2jZ8MREyJAzxdWMjrr4h5PsRp4w@mail.gmail.com
Ah, that patch. No-one investigated what e.g. SQL Server returns in the
table type column for materialized views. Or what DB2 returns for
federated tables. While the specification gives us free hands to return
an implementation-specific string, it'd be good to use what the other
DBMS's use.
- Heikki
Attachment
pgsql-odbc by date: