0

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_stream\2014_09\320_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;
0

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.

  • Yeah. I'm using PostgreSQL as well. Thanks! so i need to redefine all the view columns? – Lucas Jan 7 '16 at 23:02
  • You need to redefine the view, yes. Note that the * is not stored. It is expanded to list all columns of the table at the time when the create view was executed. – a_horse_with_no_name Jan 7 '16 at 23:04
  • I've got some errors @a_horse_with_no_name. I updated the question with the query as well some additional information... – Lucas Jan 7 '16 at 23:18
  • for such a change you need to drop the view first. Unfortunately create or replace doesn't work in that case – a_horse_with_no_name Jan 7 '16 at 23:21
  • "ERROR: cannot drop view nodes because other objects depend on it" - Any idea? @a_horse_with_no_name – Lucas Jan 7 '16 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.