更改视图中使用的 PostgreSQL 列

25-02-04 18

本篇文章给大家谈谈更改视图中使用的PostgreSQL列，同时本文还将给你拓展centos7下源码编译安装php支持PostgreSQLpostgresql手册postgresql官网下载postgr

本篇文章给大家谈谈更改视图中使用的 PostgreSQL 列，同时本文还将给你拓展centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、php – 将表和更改日志合并到PostgreSQL中的视图中、postgresql – Postgres插入视图规则与返回条款、postgresql – Postgres无法更改Ubuntu 14.04中的目录等相关知识，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

更改视图中使用的 PostgreSQL 列
centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教
php – 将表和更改日志合并到PostgreSQL中的视图中
postgresql – Postgres插入视图规则与返回条款
postgresql – Postgres无法更改Ubuntu 14.04中的目录

更改视图中使用的 PostgreSQL 列

我想让 PostegreSQL 放松一点。每次我想更改视图中使用的列时，似乎我必须删除视图，更改字段，然后重新创建视图。我可以放弃额外的保护，只告诉 PostgreSQL 让我改变字段，然后找出对视图的调整吗？

澄清： 我理解什么是视图。事实上，这是因为视图就像一个子查询，我希望我可以只更改基础表并让视图接收更改。

假设我有以下内容：

CREATE TABLE monkey(  "name" character varying(50) NOT NULL,)CREATE OR REPLACE VIEW monkey_names AS  SELECT name   FROM monkey

我真的只想在迁移脚本中执行以下操作，而不必删除并重新创建视图。

ALTER TABLE monkey ALTER COLUMN "name" character varying(100) NOT NULL

答案1

此案例的永久解决方案

为了完全避免该问题，请使用数据类型text或varchar/character varying不带长度说明符而不是character varying(n). 在手册中阅读这些数据类型。

CREATE TABLE monkey(name text NOT NULL)

如果您真的想强制执行最大长度，请创建一个CHECK约束：

ALTER TABLE monkey   ADD CONSTRAINT monkey_name_len CHECK (length(name) < 101);

您可以随时更改或删除该约束，而无需接触依赖对象（如视图），并且不会由于类型的更改而强制 Postgres 在表中写入新行（这在现代版本的 Postgres 中不再总是必要的）。

详细说明

正如@Michael 所提议的，我添加了一些更一般的信息：

PostgreSQL 中的视图不仅仅是“子查询的别名”。视图被实现为带有规则的特殊表ON SELECT TO my_view DO INSTEAD。（这就是您可以使用ALTER TABLE命令更改视图的原因。）您可以GRANT为其授予权限、添加注释甚至定义列默认值（对规则有用ON INSERT TO my_view DO INSTEAD...）。

如果您更改底层对象，您也需要更改任何依赖视图的定义查询。该ALTER VIEW语句只能更改视图的辅助属性。使用CREATE OR REPLACE VIEW更改查询-它将保留任何附加属性。

但是，如果您想更改结果列的数据类型（如手头的情况），CREATE OR REPLACE VIEW则是不可能的。你必须对DROP旧的和CREATE新的看法。这永远不会删除基础表的任何数据。但是，它会删除视图的任何其他属性，这些属性也必须重新创建。

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

1. 下载源码

$ mkdir /usr/downloads
$ wget -c http://cn2.php.net/distributions/php-5.6.20.tar.gz
$ tar -xvf php-5.6.20.tar.gz
$ mv php-5.6.20 /usr/local/src
$ cd !$ &amp; cd php-5.6.20

登录后复制

2. 阅读安装指导

$ ls -also
$ less README
$ less INSTALL

登录后复制

3. 安装依赖包

$ yum install apr apr-util apr-devel apr-util-devel prce lynx

登录后复制

4. 安装httpd

$ wget -c http://apache.fayea.com//httpd/httpd-2.4.20.tar.gz
$ tar -xvf httpd-2.4.20.tar.gz
$ cd httpd-2.4.20
$ ./configure \
--prefix=/usr/local/programs/apache2 \
--enable-rewrite \
--enable-so \
--enable-headers \
--enable-expires \
--with-mpm=worker \
--enable-modules=most \
--enable-deflate \
--enable-module=shared
$ make
$ make install
$ cd /usr/local/programs/apache2
$ cp bin/apachectl /etc/init.d/httpd ## 复制启动脚本
$ /etc/init.d/httpd start ## 启动apache服务器,访问http://localhost/
$ egrep -v ''^[ ]*#|^$'' /usr/local/apache2/conf/httpd.conf | nl ## 查看apache服务器的配置
## 将apache加入系统服务
vi /etc/rc.d/rc.local
```
/usr/local/programs/apache2/bin/apachectl start
```
$ cat /etc/rc.local

登录后复制

4. 安装postgresql

立即学习“PHP免费学习笔记（深入）”；

$ yum install readline-devel  ## 安装readline依赖
$ cd /usr/downloads
$ wget -c https://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2
$ tar -xvf postgresql-9.5.0.tar.bz2
$ cd postgresql-9.5.0
$ ./configure --prefix=/usr/local/programs/postgresql
$ make
$ su
$ make install
$ /sbin/ldconfig /usr/local/programs/postgresql/lib ## 刷新下共享动态库
$ cd /usr/local/programs/postgresql
$ bin/psql --version  ## 检查运行情况
## 开始对postgresql的配置
$ vi /etc/profile.d/postgresql.sh ## 增加环境变量,不推荐直接在/etc/profile中添加,系统更新升级时会需要merge
``` 
PATH=/usr/local/programs/postgresql:$PATH
export PATH
```
$ source /etc/profile ## 更新环境变量

## 增加用户和其他文件夹
$ adduser postgres
$ passwd postgres 
$ mkdir /usr/local/programs/postgresql/logs
$ mkdir /usr/local/programs/postgresql/data
$ chown postgres /usr/local/programs/postgresql/data
$ su - postgres

## 初始化数据库
$ ./bin/initdb -D ./data
$ ./bin/createdb test
$ ./bin/psql test
## 已有数据库，可导入data文件夹后尝试root访问，假如带密码，可能需要进一步研究下
$ ./bin/postgres -D ./data &gt;./logs/start-log-1.log 2&gt;&amp;1 &amp;
$ ./bin/psql --list  ##列出数据库
## ok，安装完成

## 自定义设置，权限控制等，可以跳过，等熟悉使用后再做
## 编辑数据库配置及权限文件：
$ vi /usr/local/programs/postgresql/data/postgresql.conf   ## 数据库配置文件
$ chown postgres postgresql.conf
$ chmod 644 postgresql.conf
$ vi /usr/local/programs/postgresql/data/pg_hba.conf   ## 权限文件
$ vi /usr/local/programs/postgresql/data/pg_ident.conf

## 设置开机自启动：
$ vi /etc/rc.d/rc.local    ## 添加如下内容
```
/usr/local/programs/postgresql/bin/postgresql start
```

登录后复制

5. 安装php

## 源码已经在第一步中下载，现在开始安装:
$ yum install libxml2 libxml2-devel libpng libpng-devel libjpeg libjpeg-devel freetype freetype-devel
$ ./configure \
--prefix=/usr/local/programs/php \
--with-apxs2=/usr/local/programs/apache2/bin/apxs \
--with-zlib \
--with-gd \
--with-jpeg-dir \
--with-png-dir \
--with-freetype-dir \
--with-zlib-dir \
--enable-mbstring \
--with-pgsql=/usr/local/programs/postgresql \
--with-pdo-pgsql=/usr/local/programs/postgresql 
$ make
$ make test
&gt; Bug #42718 (unsafe_raw filter not applied when configured as default filter) [ext/filter/tests/bug42718.phpt]  XFAIL REASON: FILTER_UNSAFE_RAW not applied when configured as default filter, even with flags
&gt; Bug #67296 (filter_input doesn''t validate variables) [ext/filter/tests/bug49184.phpt]  XFAIL REASON: See Bug #49184
&gt; Bug #53640 (XBM images require width to be multiple of 8) [ext/gd/tests/bug53640.phpt]  XFAIL REASON: Padding is not implemented yet
&gt; zend multibyte (7) [ext/mbstring/tests/zend_multibyte-07.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
&gt; zend multibyte (9) [ext/mbstring/tests/zend_multibyte-09.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
&gt;Bug #70470 (Built-in server truncates headers spanning over TCP packets) [sapi/cli/tests/bug70470.phpt]  XFAIL REASON: bug is not fixed yet

## 查阅官方的bug，发现：
&gt; id=66582: status : Closed. Fixed in master (PHP7)
&gt; id=42718: status : Assigned
&gt; id=42718: reference to id=49184, unsolved for many years
## 那就不关心了，直接装吧
$ make install
&gt; You may want to add: /usr/local/programs/php/lib/php to your php.ini include_path

## 那就按它说的设置吧
$ cp php.ini-development /usr/local/programs/php/lib/php.ini
```
include_path = ".;/usr/local/programs/php/lib/php"

## 然后，编辑httpd的设置，确保其能正确解析php文件
```
...
LoadModule php5_module modules/libphp5.so
...
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .php5

...
<ifmodule dir_module>
    DirectoryIndex index.html index.php
</ifmodule>     
```

## 重启httpd，测试
$ cd /usr/local/programs/apache2
$ bin/httpd -h
$ bin/httpd -k stop
$ bin/httpd -f conf/httpd.conf
## 默认设置的www页面在./htdocs/下，那就先去里面建一个测试页面吧
$ vi htdocs/index.php
```
<?php phpinfo(); ?>
```
$ curl http://localhost/index.php |grep postgresql
#ok

登录后复制

后续应该做的事

* 1. 启动时，不需要要手动指定配置文件
* 2. php初始化www目录设置
* 3. php 用户、权限管理等

'').addClass(''pre-numbering'').hide(); $(this).addClass(''has-numbering'').parent().append($numbering); for (i = 1; i '').text(i)); }; $numbering.fadeIn(1700); }); });

以上就介绍了centos 7下源码编译安装php支持PostgreSQL，包括了postgresql,centos 7方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

php – 将表和更改日志合并到PostgreSQL中的视图中

我的Postgresql数据库包含一个用于存储已注册实体实例的表.此表格通过电子表格上传填充. Web界面允许运算符修改所呈现的信息.但是,原始数据不会被修改.所有更改都存储在单独的表中,其中包含unique_id,column_name,value和updated_at列.

更改完成后,首先查询原始表,然后查询更改表(使用实例ID和最新更改日期,按列名称分组),将其呈现给运算符.这两个结果在PHP中合并,并在Web界面上显示.这是执行任务的一种相当严格的方式,我希望将所有逻辑保留在sql中.

我可以使用以下查询轻松选择表的最新更改：

SELECT fltr_chg.unique_id, fltr_chg.column_name, chg_val.value 
FROM changes AS chg_val
JOIN ( 
      SELECT chg_rec.unique_id, chg_rec.column_name, MAX( chg_rec.updated_at )
      FROM information_schema.columns AS source
      JOIN changes AS chg_rec ON source.table_name = 'instances'
                             AND source.column_name = chg_rec.column_name
      GROUP BY chg_rec.unique_id, chg_rec.column_name
     ) AS fltr_chg ON fltr_chg.unique_id = chg_val.unique_id
                  AND fltr_chg.column_name = chg_val.column_name;

从实例表中选择条目同样简单：

SELECT * FROM instances;

现在,如果只有一种方法可以转换前一个结果并将结果值替换为后者,基于unique_id和column_name,并仍将结果保留为表,则问题将得到解决.这可能吗？

我确信这不是最罕见的问题,而且很有可能,某些系统会以类似的方式跟踪数据的变化.如果不是通过上述方法之一(当前和寻求的解决方案),他们如何将它们应用于数据？

解决方法:

假设Postgres 9.1或更高版本.
我简化/优化了您的基本查询以检索最新值：

SELECT disTINCT ON (1,2)
       c.unique_id, a.attname AS col, c.value
FROM   pg_attribute a
LEFT   JOIN changes c ON c.column_name = a.attname
                     AND c.table_name  = 'instances'
                 --  AND c.unique_id   = 3  -- uncomment to fetch single row
WHERE  a.attrelid = 'instances'::regclass   -- schema-qualify to be clear?
AND    a.attnum > 0                         -- no system columns
AND    NOT a.attisdropped                   -- no deleted columns
ORDER  BY 1, 2, c.updated_at DESC;

我查询Postgresql目录而不是标准信息模式,因为它更快.请注意07000的特殊演员表.

现在,这给你一张桌子.您希望连续一个unique_id的所有值.
要实现这一目标,您基本上有三种选择：

>每列一个子选择(或连接).昂贵而笨重.但只有几列的有效选项.
>一个大的CASE声明.
>枢轴功能. Postgresql为此提供了crosstab() function in the additional module tablefunc.
基本说明：

> PostgreSQL Crosstab Query

带交叉表的基本数据透视表()

我完全重写了这个功能：

SELECT *
FROM   crosstab(
    $x$
    SELECT disTINCT ON (1, 2)
           unique_id, column_name, value
    FROM   changes
    WHERE  table_name = 'instances'
 -- AND    unique_id = 3  -- un-comment to fetch single row
    ORDER  BY 1, 2, updated_at DESC;
    $x$,

    $y$
    SELECT attname
    FROM   pg_catalog.pg_attribute
    WHERE  attrelid = 'instances'::regclass  -- possibly schema-qualify table name
    AND    attnum > 0
    AND    NOT attisdropped
    AND    attname <> 'unique_id'
    ORDER  BY attnum
    $y$
    )
AS tbl (
 unique_id integer
-- !!! You have to list all columns in order here !!! --
);

我将目录查找与值查询分开,因为带有两个参数的crosstab()函数分别提供了列名.缺少的值(更改中没有条目)会自动替换为NULL.这个用例的完美搭配！

假设attname与column_name匹配.排除unique_id,它扮演着特殊的角色.

完全自动化

解决your comment：有一种方法可以自动提供列定义列表.不过,这不适合胆小的人.

我在这里使用了许多高级Postgres功能：crosstab(),带动态sql的plpgsql函数,复合类型处理,高级美元引用,目录查找,聚合函数,窗口函数,对象标识符类型,…

测试环境：

CREATE TABLE instances (
  unique_id int
, col1      text
, col2      text -- two columns are enough for the demo
);

INSERT INTO instances VALUES
  (1, 'foo1', 'bar1')
, (2, 'foo2', 'bar2')
, (3, 'foo3', 'bar3')
, (4, 'foo4', 'bar4');

CREATE TABLE changes (
  unique_id   int
, table_name  text
, column_name text
, value       text
, updated_at  timestamp
);

INSERT INTO changes VALUES
  (1, 'instances', 'col1', 'foo11', '2012-04-12 00:01')
, (1, 'instances', 'col1', 'foo12', '2012-04-12 00:02')
, (1, 'instances', 'col1', 'foo1x', '2012-04-12 00:03')
, (1, 'instances', 'col2', 'bar11', '2012-04-12 00:11')
, (1, 'instances', 'col2', 'bar17', '2012-04-12 00:12')
, (1, 'instances', 'col2', 'bar1x', '2012-04-12 00:13')

, (2, 'instances', 'col1', 'foo2x', '2012-04-12 00:01')
, (2, 'instances', 'col2', 'bar2x', '2012-04-12 00:13')

 -- NO change for col1 of row 3 - to test NULLs
, (3, 'instances', 'col2', 'bar3x', '2012-04-12 00:13');

 -- NO changes at all for row 4 - to test NULLs

一个表的自动功能

CREATE OR REPLACE FUNCTION f_curr_instance(int, OUT t public.instances) AS
$func$
BEGIN
   EXECUTE $f$
   SELECT *
   FROM   crosstab($x$
      SELECT disTINCT ON (1,2)
             unique_id, column_name, value
      FROM   changes
      WHERE  table_name = 'instances'
      AND    unique_id =  $f$|| $1 || $f$
      ORDER  BY 1, 2, updated_at DESC;
      $x$
    , $y$
      SELECT attname
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = 'public.instances'::regclass
      AND    attnum > 0
      AND    NOT attisdropped
      AND    attname <> 'unique_id'
      ORDER  BY attnum
      $y$) AS tbl ($f$
   || (SELECT string_agg(attname || ' ' || atttypid::regtype::text
                       , ', ' ORDER BY attnum) -- must be in order
       FROM   pg_catalog.pg_attribute
       WHERE  attrelid = 'public.instances'::regclass
       AND    attnum > 0
       AND    NOT attisdropped)
   || ')'
   INTO t;
END
$func$ LANGUAGE plpgsql;

表实例是硬编码的,模式限定为明确的.请注意使用表类型作为返回类型. Postgresql中的每个表都自动注册了一个行类型.这必须匹配crosstab()函数的返回类型.

这将函数绑定到表的类型：

>如果您尝试DROP表,您将收到一条错误消息
> ALTER TABLE后,您的函数将失败.你必须重新创建它(没有更改).我认为这是9.1中的一个错误. ALTER TABLE不应该以静默方式破坏该函数,但会引发错误.

这表现得非常好.

呼叫：

SELECT * FROM f_curr_instance(3);

unique_id | col1  | col2
----------+-------+-----
 3        |<NULL> | bar3x

注意col1在这里是如何为NULL.
在查询中使用以显示具有其最新值的实例：

SELECT i.unique_id
     , COALESCE(c.col1, i.col1)
     , COALESCE(c.col2, i.col2)
FROM   instances i
LEFT   JOIN f_curr_instance(3) c USING (unique_id)
WHERE  i.unique_id = 3;

任何表格的完全自动化

(2016年新增.这是炸药.)
需要Postgres 9.1或更高版本. (可以与pg 8.4一起使用,但我没有费心去做.)

CREATE OR REPLACE FUNCTION f_curr_instance(_id int, INOUT _t ANYELEMENT) AS
$func$
DECLARE
   _type text := pg_typeof(_t);
BEGIN
   EXECUTE
   (
   SELECT format
         ($f$
         SELECT *
         FROM   crosstab(
            $x$
            SELECT disTINCT ON (1,2)
                   unique_id, column_name, value
            FROM   changes
            WHERE  table_name = %1$L
            AND    unique_id  = %2$s
            ORDER  BY 1, 2, updated_at DESC;
            $x$   
          , $y$
            SELECT attname
            FROM   pg_catalog.pg_attribute
            WHERE  attrelid = %1$L::regclass
            AND    attnum > 0
            AND    NOT attisdropped
            AND    attname <> 'unique_id'
            ORDER  BY attnum
            $y$) AS ct (%3$s)
         $f$
          , _type, _id
          , string_agg(attname || ' ' || atttypid::regtype::text
                     , ', ' ORDER BY attnum)  -- must be in order
         )
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = _type::regclass
   AND    attnum > 0
   AND    NOT attisdropped
   )
   INTO _t;
END
$func$ LANGUAGE plpgsql;

调用(提供表类型为NULL :: public.instances：

SELECT * FROM f_curr_instance(3, NULL::public.instances);

有关：

> Refactor a PL/pgSQL function to return the output of various SELECT queries
> How to set value of composite variable field using dynamic SQL

postgresql – Postgres插入视图规则与返回条款

我试图允许带有返回子句的insert语句进入Postgres v9.4中的视图,但我正在努力学习语法.这就是我想要调用insert语句的方法：

CREATE VIEW MyView AS SELECT a.*,b.someCol1
    FROM tableA a JOIN tableB b USING(aPrimaryKey);
INSERT INTO MyView (time,someCol) VALUES (someTime,someValue) RETURNING *;
INSERT INTO MyView (someCol) VALUES (someValue) RETURNING *;

请注意,时间的默认值为Now().这是我到目前为止：

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time,Now());
    INSERT INTO tableB (aPrimaryKey,someCol)
        VALUES (CURRVAL(''tableA_aPrimaryKey_seq''),NEW.someValue);
);

上面的工作是插入值,但我正在努力尝试找出如何添加返回的语句.我试过以下没有成功：

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time,Now())
        RETURNING *,NEW.someValue;
    INSERT INTO tableB (aPrimaryKey,NEW.someValue);
);
-- ERROR:  invalid reference to FROM-clause entry for table "new"

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    WITH a AS (INSERT INTO tableA (time)
        VALUES COALESCE(NEW.time,Now()) RETURNING *)
    INSERT INTO tableB (aPrimaryKey,someCol)
        SELECT aPrimaryKey,NEW.someValue FROM a RETURNING *;
);
-- ERROR:  cannot refer to NEW within WITH query

哎呀！有没有人知道一种方法来添加一个返回语句,该语句将第一个插入中的主键(SERIAL)和时间(TIMESTAMP WITH TIME ZONE)添加到数据库中,以及第二个插入中的someCol值？谢谢！

解决方法

你最好在这里使用INSTEAD OF INSERT触发器：

CREATE FUNCTION MyFuncName() RETURNS trigger AS $$
DECLARE
  id integer;
BEGIN
  INSERT INTO tableA (time) VALUES COALESCE(NEW.time,Now()) RETURNING aPrimaryKey INTO id;
  INSERT INTO tableB (aPrimaryKey,someCol1) VALUES (id,NEW.someValue);
  RETURN NEW;
END; $$LANGUAGE PLPGsql;

CREATE TRIGGER MyView_on_insert INSTEAD OF INSERT ON MyView
  FOR EACH ROW EXECUTE PROCEDURE MyFuncName();

检查序列的当前值以查看在另一个表中插入的内容是坏的坏习惯.即使您在一次交易中,也不要这样做.

您对RETURNING信息的问题感到困惑,因为当我阅读您的问题时,我也很困惑.在函数内部使用INTO子句填充本地声明的变量以保存记录值,然后可以在后续语句中使用这些记录值.在函数外部,使用RETURNING子句,就像在最顶部的代码片段中一样.

postgresql – Postgres无法更改Ubuntu 14.04中的目录

我正在尝试在postgres中创建和使用新数据库.当我尝试使用以下命令加载数据库时：

sudo -u postgres psql

我收到这个错误.

Could not change directory to
“/home/laptop/DropBox/js/hw/Has-Many-Relationships”: Permission denied
psql (9.3.13) Type “help” for help.

我试图以root身份更改nautilus中的文件夹权限.当文件夹更改为root(cp和cd /)时,此命令将按预期工作任何想法？

这听起来像你的shell的CWD(当前工作目录)

/home/laptop/DropBox/js/hw/Has-Many-Relationships

并且’postgres’用户无权访问该目录.

但是,在下一行中,您似乎能够与正在运行的Postgresql服务器通信,尽管您不共享下一行.

如果这是本地计算机(笔记本电脑)上的“完全一次性”数据库实例,这可能会有所帮助：

http://johnmee.com/how-to-reinstall-postgresql-on-ubuntu

此方法将完全清除所有postgres包(不仅仅是虚拟包),并允许您重新安装Postgresql.

一旦你有一个干净的Postgresql安装,试试这个,作为root：

# cd ~postgres/
# sudo -u postgres psql

并查看错误是否重复.

希望有所帮助！

今天的关于更改视图中使用的 PostgreSQL 列的分享已经结束，谢谢您的关注，如果想了解更多关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、php – 将表和更改日志合并到PostgreSQL中的视图中、postgresql – Postgres插入视图规则与返回条款、postgresql – Postgres无法更改Ubuntu 14.04中的目录的相关知识，请在本站进行查询。

本文标签：