postgres_scan can scan partiton table?and more, can scan a sql query?

[wonb168]

I hava a partition table tenant_qisefang_biz.rst_ra_skc_org_detail in greenplum,
and it has a subtable tenant_qisefang_biz.rst_ra_skc_org_detail_1_prt_p20221028,
and I use postgres_scan to create view in duckdb,

sql=f"""create view tenant_qisefang_biz.v_rst_ra_skc_org_detail as
SELECT * FROM postgres_scan('{gp_str}', 'tenant_qisefang_biz', 'rst_ra_skc_org_detail');"""
sql=f"""create view tenant_qisefang_biz.v_rst_ra_skc_org_detail_1_prt_p20221028 as
SELECT * FROM postgres_scan('{gp_str}', 'tenant_qisefang_biz', 'rst_ra_skc_org_detail_1_prt_p20221028');"""
con.execute(sql)

If directly query the subtable, cost 5.8s to get the count(),
but query the main table and use where contition, after 6 minutes can not output , I killed the query.
The table has 60 subtable like rst_ra_skc_org_detail_1_prt_p20221028.
And why count() is so slowly, even directly query the subtable ,shouldn't have been more than 1s.(In gp, less than 1s)

con.execute("select count(*) from tenant_qisefang_biz.v_rst_ra_skc_org_detail_1_prt_p20221028").fetch_df()#956098

con.execute("select count(*) from tenant_qisefang_biz.v_rst_ra_skc_org_detail where day_date = '2022-10-28'").fetch_df()#956098

I want to ask, if can add a param part_condition to spport partition table? maybe like this:

SELECT * FROM POSTGRES_SCAN('', 'public', 'mytable','2022-10-28');

And more, rather than table , can scan a sql query? just like spark or pandas:

SELECT * FROM POSTGRES_SCAN('', 'public', 'select * from mytable where ...');

If support sql query, it'll also can support pg's table funtions.
Now, I should use pandas to read it to duckdb.

sql="""select tenant_baosheng_proc.p_get_level_table_parameter_sql({p1},{p2}) ;"""
para=pa.Table.from_pandas(pd.read_sql_query(sql,conn))

[hannes]

No, this is not supported yet. We may add support for views at some point, which would be a way to do this.