Обсуждение: PL/pgsql insert into failing even with returning into clause

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 ;

Mark Bannister <mark@injection-moldings.com> writes:
> 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.

It looks like you forgot to include "INTO plpgsql-result-variable"
in the last UPDATE RETURNING in the function.  You do have that
in the earlier one, maybe you just got confused about which one
the system was complaining about?

> *Desired version:*

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

I believe the easiest way to accomplish that is something like

INSERT ... RETURNING __formuilist.* INTO composite_variable

where composite_variable could be declared RECORD, or given
the table's named rowtype.

            regards, tom lane

Thanks.  No the error is on the line I am posting about.  I realize a record is optimal but it doesn't even work in the
simpleexample.  Line ~47-49 is the error depending on which version. 

Mark B

> On Apr 16, 2020, at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Mark Bannister <mark@injection-moldings.com> writes:
>> 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.
>
> It looks like you forgot to include "INTO plpgsql-result-variable"
> in the last UPDATE RETURNING in the function.  You do have that
> in the earlier one, maybe you just got confused about which one
> the system was complaining about?
>
>> *Desired version:*
>
>> INSERT  INTO __formuilist  (formidfkey,uiname,mastertablelistxref_fkey)
>>          VALUES(_formidfkey,_uiname, _mastertablelistxref_fkey)
>>          RETURNING ROW
>>           INTO   formlistrow ;
>
> I believe the easiest way to accomplish that is something like
>
> INSERT ... RETURNING __formuilist.* INTO composite_variable
>
> where composite_variable could be declared RECORD, or given
> the table's named rowtype.
>
>            regards, tom lane

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$;