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
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. |