FEATURE REQUEST: Extend Import Foreign Schema to Views

[bradleyf]

Looking at the source code, importing foreign schema is limited to Tables only. Please extend it to work with Views as well. Thank you!

[thomasfedb]

This would be very useful, is it technically difficult?

[wesselmdw]

I would find this feature very useful

[DrLove73]

It is possible to get all info about columns from MSSQL Views using following query:

SELECT s.name as schema_name
       ,o.name as table_or_view_name
       ,c.column_id
       ,c.name as column_name
       ,t.name as data_type
       ,c.max_length
       ,c.precision
       ,c.scale
       /* start of not necessary columns */
       ,c.system_type_id
       ,c.user_type_id
       ,c.collation_name
       ,c.is_nullable
       ,c.is_ansi_padded
       ,c.is_rowguidcol
       ,c.is_identity
       ,c.is_computed
       ,c.is_filestream
       ,c.is_replicated
       ,c.is_non_sql_subscribed
       ,c.is_merge_published
       ,c.is_dts_replicated
       ,c.is_xml_document
       ,c.xml_collection_id
       ,c.default_object_id
       ,c.rule_object_id
       ,c.is_sparse
       ,c.is_column_set
       /* end of not necessary columns */
       ,o.object_id
       ,o.schema_id
       ,o.type

FROM   sys.columns c
       JOIN sys.types t
         ON t.user_type_id = c.user_type_id
            AND t.system_type_id = c.system_type_id
       JOIN sys.objects o
         ON o.object_id = c.object_id
       JOIN sys.schemas s
         ON o.schema_id = s.schema_id
WHERE (s.name = 'izp') AND (o.type = 'V')
/* WHERE (s.name = 'izp') AND (o.type = 'U') for tables */
/* WHERE (s.name = 'izp') AND (o.type = 'V') for views */
/* WHERE s.name = 'izp' filter by schema name*/
/* WHERE  object_id = OBJECT_ID('PaymentSchedule_vw') */
ORDER BY o.type DESC, o.schema_id, o.name, c.column_id

I have not looked at the code, but I am guessing tdsfdw could add "option" to switch adding views on/off and using provided data about columns to generate appropriate foreign tables (with "view" prefix).

[DrLove73]

Here is resultant info about one of my views:

schema_name	table_or_view_name	column_id	column_name	data_type	max_length	precision	scale	system_type_id	user_type_id	collation_name	is_nullable	is_ansi_padded	is_rowguidcol	is_identity	is_computed	is_filestream	is_replicated	is_non_sql_subscribed	is_merge_published	is_dts_replicated	is_xml_document	xml_collection_id	default_object_id	rule_object_id	is_sparse	is_column_set	object_id	schema_id	type
izp	UslugaRacuniSumaNis	1	UslugaId1	smallint	2	5	0	52	52	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1719013205	5	V
izp	UslugaRacuniSumaNis	2	IznosStavki	decimal	17	38	2	106	106	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1719013205	5	V
izp	UslugaRacuniSumaNis	3	CountStavki	int	4	10	0	56	56	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1719013205	5	V

[DrLove73]

In my case following query is with columns that do not have "0" for every row:

SELECT s.name as schema_name
       ,o.name as table_or_view_name
       ,c.column_id
       ,c.name as column_name
       ,t.name as data_type
       ,c.max_length
       ,c.precision
       ,c.scale
       /* start of not necessary columns */
       ,c.system_type_id
       ,c.user_type_id
       ,c.collation_name
       ,c.is_nullable
       ,c.is_ansi_padded
       /* end of not necessary columns */
       ,o.schema_id
       ,o.type

FROM   sys.columns c
       JOIN sys.types t
         ON t.user_type_id = c.user_type_id
            AND t.system_type_id = c.system_type_id
       JOIN sys.objects o
         ON o.object_id = c.object_id
       JOIN sys.schemas s
         ON o.schema_id = s.schema_id
WHERE (s.name = 'izp') AND (o.type = 'V')
/* WHERE (s.name = 'izp') AND (o.type = 'U') for tables */
/* WHERE (s.name = 'izp') AND (o.type = 'V') for views */
/* WHERE s.name = 'izp' filter by schema name*/
/* WHERE  object_id = OBJECT_ID('PaymentSchedule_vw') */
ORDER BY o.type DESC, o.schema_id, o.name, c.column_id

[DrLove73]

I have partial success with creating FOREIGN TABLES to get data from views. If I use simple view:

CREATE FOREIGN TABLE mssql.view_DuzniciSumaPoRacunuView (   
          "posaoId" INT
          ,"SumOfukupno"    decimal
)   
SERVER izp2012  
OPTIONS (   
schema_name     'izp'
, table_name    'DuzniciSumaPoRacunuView'
, row_estimate_method 'showplan_all');  

everything is fine, I get rows of data.

But, if I use try more complex view:

CREATE FOREIGN TABLE mssql.view_AvansniRacuniView ( 
           "racunId"    INT
          ,"broj"   VARCHAR(20)
          ,"tipRacunaId"    SMALLINT
          ,"naziv"  VARCHAR(50)
          ,"dRacun" DATE
          ,"Expr1"  VARCHAR(20)
          ,"Expr2"  VARCHAR(1000)
          ,"IB" VARCHAR(20)
)   
SERVER izp2012  
OPTIONS (   
schema_name     'izp'
, table_name    'AvansniRacuniView'
, row_estimate_method 'showplan_all');  

creation goes well, but when I try to get data via pgAdmin4, I get:

list index out of range

and Lazarus + ZEOS Table DOES show results.

[pgssimon]

In tds_fdw.c an edit to the following line (line 2996)... "WHERE t.table_type = 'BASE TABLE' " to... "WHERE t.table_type IN ('BASE TABLE', 'VIEW') "

should allow import from views as well as base tables

[juliogonzalez]

@pgssimon maybe you can submit a PR?

We should also add a one test for it. In fact two, one to create the stuff at MSSQL, the other to verify that it works on PostgreSQL's end. Don't worry, it's easy :-)

[cstork]

In order to include views with IMPORT FOREIGN SCHEMA for Sybase servers you also have to change the following line in function tdsImportSybaseSchema(..):

"WHERE so.type = 'U' AND su.name = "); to "WHERE so.type IN ('U','V') AND su.name = ");.

[jcarnu]

IMHO shoudn't we add options to IMPORT FOREIGN SCHEMA to include views as suggested @DrLove73 ? It seems a reasonnable thing to do this.