Re: PL/pgsql insert into failing even with returning into clause - Mailing list 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;

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