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
– ceving, Commented Jan 26, 2022 at 8: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
– Erwin Brandstetter, Commented Jan 26, 2022 at 9:49
And provide a complete function, not just a fragment. Even if it's not working (yet). — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 26, 2022 at 10:09

Erwin Brandstetter · Accepted Answer · 2022-01-26 17:34:22Z

1

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?

edited Jan 26, 2022 at 17:34

answered Jan 26, 2022 at 10:45

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prajwal Over a year ago

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.

Erwin Brandstetter Over a year ago

@Prajwal: Note that TABLE sel is just short syntax for SELECT * FROM sel.

Prajwal Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related