Select query, insert into table, and return initial query in postgres-CodePudding

I have a rather complex plpgsql stored procedure and I need to select from multiple tables and insert as well.

This is part of what I currently have.

BEGIN
RETURN query
     SELECT domains.id, webpages.id as page_id ...
     FROM domains
     LEFT JOIN domain_settings
     ON domain_settings.domain_id = domains.id
     RIGHT JOIN webpages
     ON webpages.domain_id = domains.id
     LEFT JOIN subscriptions
     ON webpages.id = subscriptions.page_id
     AND subscriptions.user_id = query_user_id
     AND subscriptions.comment_id IS NULL
     WHERE domains.domain_address = query_domain_url

  IF NOT FOUND THEN ...
END;
$$ language plpgsql;

Now, I would like add an insert query into another table using certain values from the return query before the 'if not found then' statement:

INSERT INTO page_visits (domain_id, page_id)
SELECT id, page_id FROM ?? (return query statement)

And after the insert, I want to return the initial return query values. How do I go about doing this? I tried using WITH AS statements, but I can't seem to get it to work

CodePudding user response：

A set-returning PL/pgSQL function builds the return stack while processing the function body. There is no way to access that return stack from within the same function. You could nest the function. Or use a temporary table.

But using a CTE is probably the simplest way for the cas at hand. Going out on a limb, you may be looking for something like this:

CREATE OR REPLACE FUNCTION demo(query_user_id int, query_domain_url text)
  RETURNS TABLE (c1 int, c2 int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   WITH sel AS (
      SELECT d.id, w.id as page_id ...
      FROM   webpages             w
      JOIN   domains              d  ON d.id = w.domain_id
      LEFT   JOIN domain_settings ds ON ds.domain_id = d.id
      LEFT   JOIN subscriptions   s  ON s.page_id = w.id
                                   AND s.user_id = query_user_id  -- origin?
                                   AND s.comment_id IS NULL
      WHERE  d.domain_address = query_domain_url  --  origin?
      )
   , ins AS (
      INSERT INTO tbl (col1, col2)
      SELECT main.id, sel.page_id
      FROM  (SELECT 'foo') AS main(id)
      LEFT  JOIN sel USING (id)          -- LEFT JOIN ?
      )
   TABLE sel;

   IF NOT FOUND THEN
      -- do something
   END IF;
END
$func$;

Remember, if the transaction does not commit successfully, the INSERT is also rolled back.

The final TABLE sel is just short syntax for SELECT * FROM sel. See:

Is there a shortcut for SELECT * FROM?