PL/pgsql insert into failing even with returning into clause

From: Mark Bannister <mark(at)injection-moldings(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: PL/pgsql insert into failing even with returning into clause
Date: 2020-04-16 22:40:09
Message-ID: 4da7e20a-fd41-426a-c371-8b1c51ba3fbe@injection-moldings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a simple insert into query in a PL/pgsql function.  I have a
returning into clause.

 I have tried it multiple ways but it always gives me the "query has no
destination for result data" error.

CREATE OR REPLACE FUNCTION public.tablelist_fielduiid(
    INOUT _formidfkey integer,
    INOUT _uiname text,
    INOUT _id bigint,
    INOUT _mastertablelistxref_fkey bigint)
    RETURNS record
    LANGUAGE 'plpgsql'
DECLARE
    formlistrow __formuilist%ROWTYPE;
    loCreateEntry BOOLEAN DEFAULT FALSE;
    i BIGINT;
begin

...

*Simple  version:*

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

*Desired version:*

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

*other attempts:*
    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
    ---****(at)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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2020-04-17 00:39:10 Re: PL/pgsql insert into failing even with returning into clause
Previous Message Pól Ua Laoínecháin 2020-04-12 20:25:00 Re: Inserting a constant along with field values.