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

Question

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

Add the common table expression (`with as`) you have tried to the question and explain in which way it does not work (syntax error, wrong result, ...). — ceving, Jan 26 '22 at 08:30
Why *before the 'if not found then'*? Can the `INSERT` still insert if the above `SELECT` does not find anything? Please clarify. Start by declaring your version of Postgres (always). — Erwin Brandstetter, Jan 26 '22 at 09:49
And provide a *complete* function, not just a fragment. Even if it's not working (yet). — Erwin Brandstetter, Jan 26 '22 at 10:09

Erwin Brandstetter · Accepted Answer · 2022-01-26T17:34:22.483

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?

Thank you so much for this! When I tried using WITH AS, I felt like I tried it with every possible way, but after your answer, I realized that the missing piece to the puzzle was the 'TABLE sel' after the insert. — Prajwal, Jan 26 '22 at 15:34
@Prajwal: Note that `TABLE sel` is just short syntax for `SELECT * FROM sel`. — Erwin Brandstetter, Jan 26 '22 at 17:35
Oh. I've been trying `SELECT * FROM sel' ` right from the start but it wasn't working for some reason. That's one reason why I posted here, because I was sure that it would work — Prajwal, Jan 28 '22 at 07:38

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

1 Answers1