Return value for ON CONFLICT DO NOTHING in PL/pgSQL function

Question

As instructed by this comment.

CREATE OR REPLACE FUNCTION test_upsert(
        _parent_id int, 
        _some_text text)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   a text;
BEGIN
   INSERT INTO parent_tree (parent_id, some_text)
   VALUES (_parent_id,_some_text)
   ON     CONFLICT DO NOTHING
   RETURNING 'ok' into a;
   return a;
   IF NOT FOUND THEN return 'JUZ ISTNIEJE';
   END IF;
END
$func$;

1. Not sure what this means:

   IF NOT FOUND THEN return 'JUZ ISTNIEJE';

2. If no conflict then return ok, which makes sense. But when conflict it will yield null. Is it possible to BOTH have on conflict do nothing also return not ok or when conflict happens, just raise notice parent_id should be unique?

Answer 1

I updated my referenced answer now to fix an error, which seems to have been the main cause for the confusion.

1. NOT Sure what does IF NOT FOUND THEN return 'JUZ ISTNIEJE'; mean?

"JUZ ISTNIEJE" is Polish for "already exists" - that's what the OP wants to return over there. The sole point is to return a meaningful text instead of nothing (NULL) - which seems to address your next question:

2. If no conflict then return ok, which makes sense. But when conflict it will yields null. Is it possible to BOTH have on conflict do nothing and also return not ok or when conflict happens, just raise notice parent_id should be unique.

Yes, and that's what my answer over there does, now that it's fixed. Your function becomes:

CREATE OR REPLACE FUNCTION test_upsert(_parent_id int, _some_text text)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO parent_tree (parent_id, some_text)
   VALUES (_parent_id,_some_text)
   ON     CONFLICT DO NOTHING;
   
   IF FOUND THEN
      RETURN 'ok';
   ELSE
      RAISE NOTICE 'parent_id already exists: %', _parent_id;  -- optional!
      RETURN 'not ok';  -- or whatever
   END IF;
END
$func$;