主页 / dba / 问题 / 4721
Accepted
Chau
Asked: 2011-08-18 02:36:08 +0800 CST

PostgreSQL：使用其他表中的模式和表名？

  • 772

考虑一下我的所有模式名称都在一个表中，而我的所有表名都在另一个表中的情况。

是否可以执行以下操作（伪代码）？

SELECT value FROM (SELECT schema_name FROM schemas).(SELECT table_name FROM tables)

还是我必须将查询分解为三个 SELECT？

postgresql dynamic-sql

2 个回答

  1. Best Answer

    杰克已经展示了要走的路。不过，我觉得还有改进的余地。

    测试设置：

    -- meta tables for schema and table name    
CREATE TABLE schma(schma_id int, schma text);
INSERT INTO  schma VALUES (1, 'x');

CREATE TABLE tbl(tbl_id int, tbl text);
INSERT INTO  tbl VALUES (1, 't1'), (2, 't2');

-- dummy tables to be used in example query:
CREATE TABLE t1(id int);
INSERT INTO  t1 VALUES (1),(2);

CREATE TABLE t2(foo text);
INSERT INTO  t2 VALUES ('some text'), ('some more text');

    2017年更新功能format()；2021年格式化：

    CREATE OR REPLACE FUNCTION f_dynaquery(_schma_id int
                                     , _tbl_id int
                                     , _col text
                                     , _type anyelement)
  RETURNS TABLE(col anyelement)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT %I FROM %I.%I'
    , _col
    , (SELECT schma FROM schma WHERE schma_id = _schma_id)
    , (SELECT tbl   FROM tbl   WHERE tbl_id   = _tbl_id)
   );
END
$func$;

COMMENT ON FUNCTION f_dynaquery(int, int, text, anyelement)
IS 'Query any column from a dynamically assembled tablename.
$1 .. id of schema
$2 .. id of table
$3 .. name of column
$4 .. type of column (only data type matters, not the value)';

    db<>在这里摆弄

    称呼：

    SELECT col FROM f_dynaquery(1, 1, 'id', NULL::int);
  col
-----
   1
   2

SELECT col FROM f_dynaquery(1, 2, 'foo', NULL::text);
  col
----------------
 some text
 some more text

    该函数可以返回任何类型的任何列。
    阅读有关多态类型和声明函数参数的手册。

    用于format()防御 SQL 注入。看：

    根据请求通过查询检索表名。

    为返回的列命名，以便于引用。

    原答案：

    CREATE OR REPLACE FUNCTION f_dynaquery_old(int, int, _col text, _type anyelement, OUT col anyelement)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
   SELECT ' || quote_ident(_col) || '
   FROM   ' || (
       (SELECT schma FROM schma WHERE schma_id = $1) || '.' ||
       (SELECT tbl   FROM tbl   WHERE tbl_id   = $2))::regclass;
END
$func$;

    quote_ident()通过使用和强制转换来regclass防御 SQL 注入。

    • 8

  2. 您需要动态 SQL - 可能是这样的：

    create role stack;
create schema authorization stack;
set role stack;

create or replace function f(p_schema in text, p_table in text)
                           returns setof integer language plpgsql immutable as $$
begin
  return query execute 'select value from '||p_schema||'.'||p_table;
end;$$;

create table t1(value integer);
insert into t1(value) values (1);
insert into t1(value) values (2);

create table t2(value integer);
insert into t2(value) values (1);

create table schemas(schema_name text);
insert into schemas(schema_name) values ('stack');

create table tables(table_name text);
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');

select f(schema_name, table_name) from schemas cross join tables;
 f
---
 1
 2
 1
(3 rows)

    我假设每个表都存在于每个模式中，正如问题所暗示的那样

    • 7

相关问题