带有几种模式查询的PostgreSQL存储过程 [英] postgresql stored procedure with query on several schema

问题描述

在回答了上一个问题（对多个模式的请求）后，我尝试编写一个存储过程以选择几种模式的表（每个用户都有一个模式）。

After some answer on a previous question (request over several schema), I try to write a stored procedure to select tables for several schemas (Each user have a schema).

create or replace public.select_simulations() returns setof simulation as $$
declare 
    users pg_user%ROWTYPE;
    simu simulation%ROWTYPE;
begin
    for users in select usename from pg_user where usename <> 'postgres' loop
        for simu in select id, name from (users.usename).simulation loop            
            return next simu;
        end loop;
    end loop;
end; 
$$

，但它不接受（用户.usename）.simulation ，并且在没有括号的情况下产生了错误（似乎是在搜索子字段，而不是模式）...

but it doesn't accept the (users.usename).simulation, and without the parenthesis it produced an error (seems to search a sub field, not a schema)...

那么告诉 users.usename 是模式名称的正确语法是什么？

So what is the correct syntax to tell that users.usename is a schema name ?

谢谢您的

推荐答案

您可以查看执行中的控制结构：

You could take a look at the for-in-execute control structure:

FOR record_or_row IN EXECUTE text_expression LOOP 
    statements
END LOOP [ label ];

http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

类似的东西

...

for users in select usename from pg_user where usename <> 'postgres' loop
    for simu in execute 'select id, name from '||quote_ident(users.usename)||'.simulation' loop
        return next simu;
    end loop;
end loop;
...

这篇关于带有几种模式查询的PostgreSQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！