PostgreSQL动态表访问 [英] PostgreSQL dynamic table access

问题描述

我有一个产品架构和一些表格。

products 模式有一个 id ，通过 id 我可以得到这个表名，例如

I have a products schema and some tables there.
Each table in products schema has an id, and by this id I can get this table name, e.g.

products
    \ product1
    \ product2
    \ product3

我需要从动态访问适当的产品中选择信息，例如

I need to select info from dynamic access to appropriate product, e.g.

SELECT * FROM 'products.'(SELECT id from categories WHERE id = 7);

当然，这不工作...

我如何在PostgreSQL中做这样的事情？

Of course, this doesn't work...
How I can do something like that in PostgreSQL?

推荐答案

OK，我找到了一个解决方案：

OK, I found a solution:

CREATE OR REPLACE FUNCTION getProductById(cid int) RETURNS RECORD AS $$
    DECLARE
    result RECORD;

    BEGIN
        EXECUTE 'SELECT * FROM ' || (SELECT ('products.' || (select category_name from category where category_id = cid) || '_view')::regclass) INTO result;

        RETURN result;
    END;
$$ LANGUAGE plpgsql;

并选择：

SELECT * FROM getProductById(7) AS b (category_id int, ... );

适用于PostgreSQL 9.x

works for PostgreSQL 9.x

这篇关于PostgreSQL动态表访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！