How do I end function when X so my function looks like this:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with 
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
end; $$


I need the function to check if a1||a2 in table_1.a12 before running the inserts and if it is in skip that insert and move onto the next.

I tried:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with 
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
exception when select (select a1||a2) in (select a12 from table_1) then
end; $$


but this just causes the query to stall out
You can simplify to:

CREATE OR REPLACE FUNCTION pg_temp.insert_stuff(_a1 text, _a2 text, _a3 int)
  RETURNS TABLE (id_i int, id_j int) AS
$func$
BEGIN 
   IF EXISTS (SELECT FROM table_1 WHERE a12 = _a1||a2) THEN  -- see below
      RETURN QUERY
      SELECT -1, NULL;
      -- RAISE EXCEPTION 'Some informative msg here'  -- alternative?
   ELSE
      RETURN QUERY
      SELECT insert_here(_a1, _a2)
           , insert_there(_a3, _a2);
   END IF;
END
$func$  LANGUAGE plpgsql;


Note the clearer (and faster) IF EXISTS. See:


PL/pgSQL checking if a row exists


Either variant might fail "unexpectedly" if either input parameter or the column a12 can be NULL! You would have to define desired behavior.

Subtle difference: Since I replaced your CTE construct with a simple SELECT, the function always returns at least one now, even if either nested function (insert_here() or insert_there()) returns no row. Your version would return nothing in this case, which may be a sneaky bug if either of the nested functions can return no row.

I am assuming Postgres 10 or later (you didn't declare). Else, depending on the undeclared nature of the nested functions, you might need to consider:


What is the expected behaviour for multiple set-returning functions in select clause?