# 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

• Please clarify: and if it is in skip that insert and move onto the next. And always declare your version of Postgres. Also relevant: how many rows can each nested function return? (0-n?) – Erwin Brandstetter Sep 11 '18 at 16:14

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:

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:

one solution I have found :

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$begin if (select (select a1||a2) in (select a12 from table_1)) then return query select -1; else 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 if; end;$$


side note : this output is a record of insert, and when it errors it gives guidance for entries that need the human touch because they require a complex revision to match data we need in the database