为 PostgreSQL 表创建别名

Create Alias for PostgreSQL Table

提问by Phillip Boushy

I have a table called assignments. I would like to be able to read/write to all the columns in this table using either assignments.column or homework.column, how can I do this?

我有一张表叫做作业。我希望能够使用 assignments.column 或 homework.column 读取/写入此表中的所有列，我该怎么做？

I know this is not something you would normally do. I need to be able to do this to provide backwards compatibility for a short period of time.

我知道这不是你通常会做的事情。我需要能够做到这一点，以在短时间内提供向后兼容性。

We have an iOS app that currently does direct postgresql queries against the DB. We're updating all of our apps to use an API. In the process of building the API the developer decided to change the name of the tables because we (foolishly) thought we didn't need backwards compatibility.

我们有一个 iOS 应用程序，它目前直接针对数据库执行 postgresql 查询。我们正在更新我们所有的应用程序以使用 API。在构建 API 的过程中，开发人员决定更改表的名称，因为我们（愚蠢地）认为我们不需要向后兼容。

Now, V1.0 and the API both need to be able to write to this table so I don't have to do some voodoo later to transfer/combine data later... We're using Ruby on Rails for the API.

现在，V1.0 和 API 都需要能够写入这个表，所以我以后不必做一些巫术来传输/组合数据......我们使用 Ruby on Rails 作为 API。

回答by hegemon

With Postgres 9.3 the following should be enough:

对于 Postgres 9.3，以下内容就足够了：

CREATE VIEW homework AS SELECT * FROM assignments;

CREATE VIEW homework AS SELECT * FROM assignments;

It works because simple views are automatically updatable (see docs).

它之所以有效，是因为简单的视图可以自动更新（请参阅文档）。

回答by Erwin Brandstetter

Building on your work in progress:

以您正在进行的工作为基础：

Trigger function

触发功能

CREATE OR REPLACE FUNCTION trg_ia_insupdel()
  RETURNS TRIGGER AS
$func$
DECLARE
   _tbl  CONSTANT regclass := 'iassignments_assignments';
   _cols text;
   _vals text;
BEGIN

CASE TG_OP
WHEN 'INSERT' THEN

   INSERT INTO iassignments_assignments
   VALUES NEW;

   RETURN NEW;

WHEN 'UPDATE' THEN
   SELECT INTO _cols, _vals
          string_agg(quote_ident(attname), ', ')   -- incl. pk col!
         ,string_agg('n.' || quote_ident(attname), ', ')
   FROM   pg_attribute
   WHERE  attrelid = _tbl        -- _tbl converted to oid automatically
   AND    attnum > 0             -- no system columns
   AND    NOT attisdropped;      -- no dropped (dead) columns

   EXECUTE format('
      UPDATE %s t
      SET   (%s) = (%s)
      FROM  (SELECT ().*) n
      WHERE    t.published_assignment_id
          = ().published_assignment_id' -- match to OLD value of pk
    , _tbl, _cols, _vals)        -- _tbl converted to text automatically
   USING NEW, OLD;

   RETURN NEW;

WHEN 'DELETE' THEN
   DELETE FROM iassignments_assignments
   WHERE  published_assignment_id = OLD.published_assignment_id;

   RETURN OLD;
END CASE;

RETURN NULL;  -- control should never reach this

END
$func$ LANGUAGE plpgsql;

Trigger

扳机

CREATE TRIGGER insupbef
INSTEAD OF INSERT OR UPDATE OR DELETE ON assignments_published
FOR EACH ROW EXECUTE PROCEDURE trg_ia_insupdel();

Notes

笔记

• Dynamic SQL (in the UPDATEsection) is not strictly necessary, only to cover future changes to the table layout automatically. The names of the table and the pk are still hard coded.

• plpgsql assignment operator is :=

• Simpler and probably cheaper without sub-block(like you had).

• Using (SELECT ($1).*)instead of the shorter VALUES $1to preserve column names.

• My naming convention: I prepend trg_for trigger functions, followed by an abbreviation indicating the target table and finally one or more of the the tokens ins, upand delfor INSERT, UPDATEand DELETErespectively. The name of the trigger is a copy of the function name, stripped of the first two parts. This is purely a matter of convention and taste but has proven useful for me since the names tell the purpose and are still short enough.

• More explanation in the related answer that has already been mentioned:
  Update multiple columns in a trigger function in plpgsql

• 动态 SQL（在UPDATE部分）不是绝对必要的，只是为了自动覆盖未来对表布局的更改。表的名称和 pk 仍然是硬编码的。

• plpgsql 赋值运算符是 :=

• 没有子块更简单，可能更便宜（就像你一样）。

• 使用(SELECT ($1).*)而不是较短的VALUES $1来保留列名。

• 我的命名约定：我在前面加上trg_用于触发功能，随后的缩写指示目标表和最后的一个或多个令牌的ins，up并del为INSERT，UPDATE和DELETE分别。触发器的名称是函数名称的副本，去掉了前两部分。这纯粹是惯例和品味的问题，但事实证明对我有用，因为名称说明了目的并且仍然足够短。

• 已经提到的相关答案中的更多解释：
  Update multiple columns in a trigger function in plpgsql

回答by Phillip Boushy

This is where I am with the trigger functions so far, any feedback would be greatly appreciated. It's a combination of http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/and Update multiple columns in a trigger function in plpgsql

到目前为止，这是我使用触发器功能的地方，任何反馈都将不胜感激。它是http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/和plpgsql 中触发器函数中更新多列的组合

Table: iassignments_assignments

表：iassignments_assignments

Columns:

列：

published_assignment_id
name
filepath
filename
link
teacher
due date
description
published
classrooms

View: assignments_published - SELECT * FROM iassignments_assignments

查看：assignments_published - SELECT * FROM iassignments_assignments

Trigger Function for assignments_published

assignments_published 的触发函数

CREATE OR REPLACE FUNCTION assignments_published_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   BEGIN
      IF TG_OP = 'INSERT' THEN
        EXECUTE format('INSERT INTO %s SELECT ().*', 'iassignments_assignments')
        USING NEW;
        RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
        DECLARE
          tbl = 'iassignments_assignments';
          cols text;
          vals text;
        BEGIN
          SELECT INTO cols, vals
           string_agg(quote_ident(attname), ', ')
           ,string_agg('x.' || quote_ident(attname), ', ')
          FROM   pg_attribute
          WHERE  attrelid = tbl
          AND    NOT attisdropped   -- no dropped (dead) columns
          AND    attnum > 0;        -- no system columns

          EXECUTE format('
          UPDATE %s t
          SET   (%s) = (%s)
          FROM  (SELECT ().*) x
          WHERE  t.published_assignment_id = ().published_assignment_id'
          , tbl, cols, vals)
          USING NEW, OLD;

          RETURN NEW;
        END
      ELSIF TG_OP = 'DELETE' THEN
       DELETE FROM iassignments_assignments WHERE published_assignment_id=OLD.published_assignment_id;
       RETURN NULL;
      END IF;
      RETURN NEW;
    END;
$function$;

Trigger

扳机

CREATE TRIGGER assignments_published_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON
assignments_published FOR EACH ROW EXECUTE PROCEDURE assignments_published_trigger_func();

---

---

Table: iassignments_classes

表：iassignments_classes

Columns:

列：

class_assignment_id
guid
assignment_published_id

View: assignments_class - SELECT * FROM assignments_classes

查看：assignments_class - SELECT * FROM assignments_classes

Trigger Function for assignments_class

assignments_class 的触发函数

**I'll create this function once I have received feedback on the other and know it's create, so I (hopefully) need very little changes to this function.

**一旦我收到另一个函数的反馈并知道它是创建的，我就会创建这个函数，所以我（希望）需要对这个函数进行很少的更改。

Trigger

扳机

CREATE TRIGGER assignments_class_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON
assignments_class FOR EACH ROW EXECUTE PROCEDURE assignments_class_trigger_func();