How to add SQL table length as a column through select query?-CodePudding

Context: I want to add an extra column in my table which specifies the length of the table. This will be used by me when I union multiple charts - knowing the length of the original chart will be used in a computation function (which I'll do in a UDF python function).

Question: how can I add the length of the table as a column to my view. Currently, my columns look like this:

Primary key | attr1 | attr2 | attr3 | attr4

I want them to look like this:

Primary key | attr1 | attr2 | attr3 | attr4 | len_table

I tried using count(*) then joining the table but obviously that didn't work as count returns an integer and there's no attribute to join "on"

CodePudding user response：

CREATE temp TABLE source (
    col1 int,
    col2 int,
    col3 text
);

INSERT INTO source
SELECT
    i,
    i   1,
    'i=' || i || 'i 1 = ' || i   1
FROM
    generate_series(1, 4) g (i);

CREATE OR REPLACE VIEW myview AS
SELECT
    col1,
    col2,
    col3,
    'hello' AS col4,
    count(*) OVER (ORDER BY col1) AS col5
FROM
    source
WHERE
    col1 < 4;

TABLE myview;

UPDATE
    myview
SET
    col1 = 3
WHERE
    col1 = 2
RETURNING
    *;

Hope this work for you. Since you have count aggregate function then you cannot directly update this view.