In my function I use construction
insert into Table1(a, b, c)
select a, b, c from Table2
And I need id
of last inserted row to Table1 FROM THIS CALL OF FUNCTION
The way – select max(id) from Table1 is not correct.
In documentation i search about returning
, but this not
work for multiple insert
do $$
declare _query int;
begin
drop table if exists tTest;
create temp table tTest (id serial primary key, name text);
--work
insert into tTest(name)
values ('name') returning id into _query;
--but when i use multiple insert - error
insert into tTest(name)
values ('name'), ('name1'), ('name2') returning id into _query;
RAISE NOTICE '%', _query;
end;
$$;
I try using create array from insert, using CTE, but this not work. Have any way to get last id?
I need analog scope_identity
in t-sql.
>Solution :
Use a CTE in combination with MAX:
DO
$$
DECLARE
_id INT;
BEGIN
DROP TABLE IF EXISTS ttest;
CREATE TEMP TABLE ttest
(
id SERIAL PRIMARY KEY,
name TEXT
);
--work
INSERT INTO ttest(name)
VALUES ('name')
RETURNING id INTO _id;
--Use a CTE and MAX to get the highest number:
WITH i AS (
INSERT INTO ttest (name)
VALUES ('name'), ('name1'), ('name2')
RETURNING id)
SELECT MAX(id)
FROM i
INTO _id;
RAISE NOTICE 'Last id: %', _id;
END;
$$;