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

查看:307
本文介绍了PostgreSQL动态表访问的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆