New column to a base table

I want to add a new column to a base table, and add that new column to an existing view…

So all selects/updates/deletes translate back to the original table

How can I do that?

Thanks

——————— UPDATED QUESTION ——————————-

Table: gorfs.inode_segments
View: gorfs.nodes

View query pulled from some_db repository, ddl_stream2014_09320_gorf.sql with the new column added.

Query currently returns error
ERROR: cannot change name of view column “parent_inode_id” to “nfs_full_path”

BEGIN;


ALTER TABLE inode_segments ADD COLUMN nfs_file_path VARCHAR(255) DEFAULT NULL;


CREATE OR REPLACE VIEW gorfs.nodes AS
SELECT p.full_path AS node_full_path,
       p.nfs_file_path AS nfs_full_path,
       h.st_ino AS parent_inode_id,
       t.st_ino AS inode_id,
       (CASE
            WHEN (p.st_ino_target = 2) THEN NULL
            ELSE p.segment_index
        END) AS relative_path,
       CAST(t.st_mode AS BIGINT) AS raw_mode,
       f.constant_name AS object_type,
       (CAST((gorfs.mode_t_bits('S_ISUID') & t.st_mode) AS INT) != 0) AS setuid,
       (CAST((gorfs.mode_t_bits('S_ISGID') & t.st_mode) AS INT) != 0) AS setgid,
       (CAST((gorfs.mode_t_bits('S_ISVTX') & t.st_mode) AS INT) != 0) AS sticky,
       CAST(RIGHT(CONCAT((gorfs.mode_t_bits('S_IRWXU') | gorfs.mode_t_bits('S_IRWXG') | gorfs.mode_t_bits('S_IRWXO')) & t.st_mode), 9) AS BIT(9)) AS permissions,
       t.st_nlink AS links_count,
       t.st_uid AS owner_uid,
       t.st_gid AS owner_gid,
       t.st_size AS data_length,
       t.st_atime AS last_accessed,
       t.st_mtime AS last_modified,
       t.st_ctime AS last_changed,
       t.checksum_md5 AS checksum_md5,
       CAST((mst.media_type || '/' || mst.subtype_string) AS TEXT) AS media_type,
       (CASE
            WHEN (f.constant_name = 'S_IFLNK') THEN
                   (SELECT convert_from(ls.segment_data, 'UTF8')
                    FROM gorfs.inode_segments AS ls
                    WHERE (ls.st_ino = p.st_ino_target))
            ELSE NULL
        END) AS target,
       (CASE
            WHEN (f.constant_name = 'S_IFREG') THEN
                   (SELECT string_agg(fs.segment_data, ''
                                      ORDER BY fs.segment_index ASC)
                    FROM gorfs.inode_segments AS fs
                    WHERE (fs.st_ino = p.st_ino_target))
            ELSE NULL
        END) AS file_data,
       (t.external_size IS NOT NULL) AS is_external,
       t.external_size AS data_length_target
FROM ( ( ( gorfs.inode_segments AS p
          INNER JOIN gorfs.dir_inodes AS h ON (h.st_ino = p.st_ino) )
        INNER JOIN gorfs.inodes AS t ON (t.st_ino = p.st_ino_target) )
      INNER JOIN gorfs.mode_t_flags() AS f ON (f.bits = (gorfs.mode_t_bits('S_IFMT') & t.st_mode)) )
LEFT JOIN public.media_subtypes AS mst ON (mst.media_subtype_id = t.media_subtype_id);

 COMMENT ON VIEW gorfs.nodes IS 'The main access interface to the file system abstraction. INSERTs, UPDATEs and DELETEs translate into actions against the hierarchical table structure';

 COMMENT ON COLUMN gorfs.nodes.node_full_path IS '"Primary key" of this relation: the full path of the object. Changes here renest/rename objects';

 COMMENT ON COLUMN gorfs.nodes.nfs_full_path IS 'The file path of the NFS disk';

 COMMENT ON COLUMN gorfs.nodes.parent_inode_id IS 'Inode number the object is nested on. Ignored in input: change node_full_path to nest somewhere else';

 COMMENT ON COLUMN gorfs.nodes.inode_id IS 'Inode number the object name points to';

 COMMENT ON COLUMN gorfs.nodes.relative_path IS 'Last path component (the actual content of the name). Changes here are ignored. Change node_full_path to alter';

 COMMENT ON COLUMN gorfs.nodes.raw_mode IS 'Decimal integer representation of st_mode. Useful for applications that internally use [l]stat(2). Read only';

 COMMENT ON COLUMN gorfs.nodes.object_type IS 'Object type constant. Read only. See gorfs.mode_t_flags(), or stat(2) - man 2 stat - for meaning';

 COMMENT ON COLUMN gorfs.nodes.setuid IS 'SetUID bit';

 COMMENT ON COLUMN gorfs.nodes.setgid IS 'SetGID bit';

 COMMENT ON COLUMN gorfs.nodes.sticky IS 'Sticky bit';

 COMMENT ON COLUMN gorfs.nodes.permissions IS 'The last 9 bits of st_mode (the permissions). Changes here are reflected in the actual inodes';

 COMMENT ON COLUMN gorfs.nodes.links_count IS 'Number of hard links pointing to the inode. Read only';

 COMMENT ON COLUMN gorfs.nodes.owner_uid IS 'Owner user ID';

 COMMENT ON COLUMN gorfs.nodes.owner_gid IS 'Owner group ID';

 COMMENT ON COLUMN gorfs.nodes.data_length IS 'Content length in bytes. Read only. For fat links, the remote lenght is displayed instead. Meaningless for anything else than regular files or fat links';

 COMMENT ON COLUMN gorfs.nodes.last_accessed IS 'Timestamp of last access';

 COMMENT ON COLUMN gorfs.nodes.last_modified IS 'Timestamp of last content change';

 COMMENT ON COLUMN gorfs.nodes.last_changed IS 'Timestamp of last meta data change';

 COMMENT ON COLUMN gorfs.nodes.data_length IS 'Content length in bytes. Read only';

 COMMENT ON COLUMN gorfs.nodes.checksum_md5 IS 'Checksum of the file. Calculated internally if not supplied for regular files with internal storage';

 COMMENT ON COLUMN gorfs.nodes.media_type IS 'MIME type. Must be supplied (internal determination not implemented. yet?)';

 COMMENT ON COLUMN gorfs.nodes.is_external IS 'Read only. Boolean that indicates that a symlink is a fat link (external). Meaningless for non-symlinks';

 COMMENT ON COLUMN gorfs.nodes.target IS 'For symlinks/fat links only: target path';

 COMMENT ON COLUMN gorfs.nodes.file_data IS 'Binary content of the files (for internal storage only). Not meaningful for non-regular files. Read only (one has to delete the old symlink in order to re-target)';

 COMMENT ON COLUMN gorfs.nodes.data_length_target IS 'Size of the remote data for fat links. Meaningless for every other object type';

 GRANT
SELECT,
INSERT,
UPDATE,
DELETE ON TABLE gorfs.nodes TO some_dc;


ROLLBACK;

For Postgres just add the column, then re-create the view:

alter table base_table
   add column new_column varchar(20);

create or replace view base_view
as 
select *
from base_table;

If the new column is in the middle of the select list, you need to drop the view first. Unfortunately create or replace doesn’t work then.

Don’t know about MySQL.