Postgres CTE Insert and get count-CodePudding

Here's a CTE query. After the insert, I want to get the updated count. The insert happens fine but the count returns the count before the INSERT and does not include the new row. Can you please let me know if I am doing something wrong here?

WITH reply_data(id, threadid, commentid, userid, description, created, updated) AS (
    VALUES ('27c12e17-b105-48fd-897b-82e5965ab15a'::uuid,
            'bbe04e77-0e53-4716-b001-81e7dbf40d70'::uuid,
            'fd2513fb-5e92-4a40-a295-6c122c325166'::uuid,
            '5b3a6120-233e-4b77-9160-c08c484db31b'::uuid,
            'Manual Reply to comment from SQL',
            now(),
            now())
),
     reply_insert AS (
         INSERT INTO replies (id, threadid, commentid, userid, description, created, updated)
             SELECT rd.id, rd.threadid, rd.commentid, rd.userid, rd.description, rd.created, rd.updated
             FROM reply_data rd
             RETURNING id, commentid
     ),
     user_reply_insert as (
         INSERT INTO user_replies (userid, replyid)
             SELECT rd.userid, rd.id FROM reply_data rd
             RETURNING userid
     ),
     replyCount as (
         select count(*) as repliescount
         from replies r,
              reply_data rd
         where r.commentid = rd.commentid
     )

SELECT repliescount FROM replyCount;

CodePudding user response：

According to Postgres document all sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database.

You would need two statements (in a single transaction) for what you are trying to do OR calculate with total data when inserted in CTE:

WITH reply_data(id, threadid, commentid, userid, description, created, updated) AS (
    VALUES ('27c12e17-b105-48fd-897b-82e5965ab15a'::uuid,
            'bbe04e77-0e53-4716-b001-81e7dbf40d70'::uuid,
            'fd2513fb-5e92-4a40-a295-6c122c325166'::uuid,
            '5b3a6120-233e-4b77-9160-c08c484db31b'::uuid,
            'Manual Reply to comment from SQL',
            now(),
            now())
),
     reply_insert AS (
         INSERT INTO replies (id, threadid, commentid, userid, description, created, updated)
             SELECT rd.id, rd.threadid, rd.commentid, rd.userid, rd.description, rd.created, rd.updated
             FROM reply_data rd
             RETURNING id, commentid
     ),
     user_reply_insert as (
         INSERT INTO user_replies (userid, replyid)
             SELECT rd.userid, rd.id FROM reply_data rd
             RETURNING userid
     ),
     replyCount as (
         select count(*)   (select count(*) from reply_insert) as repliescount
         from replies r,
              reply_data rd
         where r.commentid = rd.commentid
     )

SELECT repliescount FROM replyCount;