Add column to View without using ALTER TABLE-CodePudding

I have a CTE, something like

CREATE VIEW someView AS WITH foo AS (
    SELECT id FROM bar
        JOIN ...
), foo2 AS (
    SELECT whatever FROM bar2 ...
), ... AS ( ...
), lastResult AS (
   ...
) SELECT * FROM lastResult;

I want to add an id column to the last SELECT Statement (SELECT * FROM lastResult;). On an existing table you would do it like this

ALTER TABLE lastResult ADD COLUMN id SERIAL PRIMARY KEY;

Is that possible with a CTE and a View?

CodePudding user response：

I think you can use the ROW_NUMBER. here is a sample

CREATE VIEW someView AS WITH foo AS (
    SELECT id FROM bar
        JOIN ...
), foo2 AS (
    SELECT whatever FROM bar2 ...
), ... AS ( ...
), lastResult AS (
   ...
) SELECT * ,
    ROW_NUMBER () OVER (ORDER BY Your_Column) as Id
FROM
FROM lastResult;