PL/pgsql insert into failing even with returning into clause - Mailing list pgsql-novice

    INSERT  INTO __formuilist  (formidfkey,uiname,mastertablelistxref_fkey)
         VALUES(_formidfkey,_uiname, _mastertablelistxref_fkey)
         RETURNING id,formidfkey,uiname,mastertablelistxref_fkey
          INTO   formlistrow.id,formlistrow.formidfkey, formlistrow.uiname,  formlistrow.mastertablelistxref_fkey;

Here's the whole function if that helps:

CREATE OR REPLACE FUNCTION public.tablelist_fielduiid(
    INOUT _formidfkey integer,
    INOUT _uiname text,
    INOUT _id bigint,
    INOUT _mastertablelistxref_fkey bigint)
    RETURNS record
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
DECLARE
    formlistrow __formuilist%ROWTYPE;
    loCreateEntry BOOLEAN DEFAULT FALSE;
    i BIGINT;
begin

_uiname := LOWER(_uiname);

CASE
    WHEN (_id IS NOT NULL) THEN --you sent the id so retrieve the row
        SELECT * INTO formlistrow FROM __formuilist WHERE id = _id;
        IF NOT FOUND THEN
            raise exception 'id not found in _formuilist:%1', _id
            return;
        END IF;
       
    WHEN ( _formidfkey IS NOT  NULL) and (_uiname IS NOT NULL) THEN -- find entry by formidfkey and field name
        SELECT * INTO formlistrow FROM __formuilist
                        where formidfkey = _formidfkey
                           AND uiname = _uiname;
        IF NOT FOUND THEN
            loCreateEntry := TRUE;
        end if;
    ELSE
      raise exception 'values required for formuiid or formidfkey and fieldname';
      return;
end case;

-- we have found the right row or failed
i:=formlistrow.mastertablelistxref_fkey; --troubleshooting
CASE
    WHEN loCreateEntry then
        INSERT  INTO __formuilist  (formidfkey,uiname,mastertablelistxref_fkey)
         VALUES(_formidfkey,_uiname, _mastertablelistxref_fkey)
         RETURNING id,formidfkey,uiname,mastertablelistxref_fkey
          INTO   formlistrow.id,formlistrow.formidfkey, formlistrow.uiname,  formlistrow.mastertablelistxref_fkey;
         -- INTO i; --formlistrow;
    if NOT found then
        raise exception 'unable to insert row into __formuilist';
        return;
    end if;

   WHEN  (_mastertablelistxref_fkey IS NOT NULL )
      and NOT (formlistrow.mastertablelistxref_fkey = _mastertablelistxref_fkey))          THEN

    ---****@TODO  this never trips......?????**********

    -- may be we just want to update the mastertablelistxref_fkey
    formlistrow.mastertablelistxref_fkey := _mastertablelistxref_fkey;
    UPDATE __formuilist SET
          formidfkey = formlistrow.formidfkey
          ,uiname = formlistrow.uiname
          ,mastertablelistxref_fkey = formlistrow.mastertablelistxref_fkey
         WHERE id = formlistrow.id
         RETURNING  formlistrow;
         if NOT found then
            raise exception 'unable to update row of __formuilist';
            return;
        end if;
    ELSE
        --nothing to do
end case;

_formidfkey := formlistrow.formidfkey;
_uiname:= formlistrow.uiname;
_id := formlistrow.id;
_mastertablelistxref_fkey:= formlistrow.mastertablelistxref_fkey;

end
$BODY$;

--

Mark B