0

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

  • 1
    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
1

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:

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.