Return last id of multiple insert

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;
    drop table if exists tTest;
    create temp table tTest (id serial primary key, name text);

    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;

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:

        _id INT;
        DROP TABLE IF EXISTS ttest;
        CREATE TEMP TABLE ttest
            id   SERIAL PRIMARY KEY,
            name TEXT

        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;

Leave a Reply