Thread: 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 ;
VALUES(_formidfkey,_uiname, _mastertablelistxref_fkey)
RETURNING id,formidfkey,uiname,mastertablelistxref_fkey
INTO formlistrow.id,formlistrow.formidfkey, formlistrow.uiname, formlistrow.mastertablelistxref_fkey;
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
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
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$;
Mark