Re: PL/pgsql insert into failing even with returning into clause

On 4/16/2020 5:40 PM, Mark Bannister wrote:
>
> 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;
>
>
>
>
I must have had some other error that the parser wasn't catching,
because an if statement was working correctly either.  In the this
method worked:

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

Results are saved in formlistrow which is s a from from the table.

Complete function that works:

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;

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
        -- did not find and entry for this form field combo so create it
            INSERT  INTO __formuilist 
(formidfkey,uiname,mastertablelistxref_fkey)
                     VALUES(_formidfkey,_uiname, _mastertablelistxref_fkey)
                     RETURNING *
                      INTO   formlistrow;
                   
                if NOT found then
                    raise exception 'unable to insert row into
__formuilist';
                    return;
                end if;
        end if;
    ELSE
      raise exception 'values required for formuiid or formidfkey and
fieldname';
      return;
end case;

-- we have found the right row or failed

IF ( (_mastertablelistxref_fkey IS NOT NULL )
    and NOT (formlistrow.mastertablelistxref_fkey =
_mastertablelistxref_fkey))
         THEN
    -- 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 *
         INTO formlistrow;
         if NOT found then
            raise exception 'unable to update row of __formuilist';
            return;
        end if;
   
   
end IF;

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

end
$BODY$;

--

Mark