Question 250555 in PostgreSQL Question posted in
The official documentation can be found here.

How to return a mapping of old_ids and duplicated row's new_ids from a PostgreSQL function

I am trying to write a postgreSQL function that duplicates all rows of a table by for the specified condition and return the mapping of existing row ids and duplicated row ids.

Here is the example data that I want to work with.

CREATE TABLE foo (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 6),
  foo_content text NOT NULL,
  condition_col integer NOT  NULL
);

INSERT INTO foo OVERRIDING SYSTEM VALUE VALUES
(  1 , 'Sing, O goddess', 99),
(  2 , 'the anger of Achilles', 99),
(  3 , 'son of Peleus', 99),
(  4 , 'that brought countless ills', 2),
(  5 , 'upon the Achaeans', 3);

SELECT * FROM foo;

This is my attempt of writing the SQL function that is able to duplicate the entries:

CREATE OR REPLACE FUNCTION duplicate_entries(condition INT, new_condition INT)
RETURNS TABLE (
        old_id INT,
        new_id INT) AS
$$
BEGIN
    RETURN QUERY (
        WITH select_cte AS (
            SELECT id
            FROM foo f
            WHERE f.condition_col = condition
        ), insertion_cte AS (
            INSERT INTO foo (foo_content, condition_col)
            SELECT f.foo_content, new_condition
            FROM select_cte s
            JOIN foo f ON s.id = f.id
            RETURNING id
        )
        --- how to I return a mapping of ids from here?
    );
END;
$$ LANGUAGE plpgsql;

-- To call the function and get the result
SELECT * FROM duplicate_entries(99, 100);

-- To see the updated 'foo' table
SELECT * FROM foo;

Here although I have achieved the desired function, I am unable to return the mapping of old_ids and new_ids from this function.

The returning table should look like this. I have tried to use a UNION ALL query but does not seem to do the trick. I think maybe a temp table would help in this case but I don’t want to use a temp table as that may be a bad practice.

This is is what my desired output should look like:

old_ids new_ids
1 6
2 7
3 8
Tags:

2

Answers


  1. 0

    You can only use literals or stuff from the inserted data in your returning clause. You can iterate over the records you’re duplicating and inject them as literals: demo

    CREATE OR REPLACE FUNCTION duplicate_entries(condition INT, new_condition INT)
RETURNS TABLE (
        old_id INT,
        new_id INT) AS
$f$
DECLARE v_old_id int;
        v_foo_content text;
        v_new_condition int;
BEGIN
    FOR v_old_id, v_foo_content, v_new_condition IN 
            SELECT id, foo_content, condition_col
            FROM foo f
            WHERE f.condition_col = condition
    LOOP
    RETURN QUERY (
        WITH insertion_cte AS (
            INSERT INTO foo AS n (foo_content, condition_col) 
            VALUES (v_foo_content, v_new_condition)
            RETURNING v_old_id as old_id, n.id as new_id
        )
        select * from insertion_cte i
    );
    END LOOP;
END;
$f$ LANGUAGE plpgsql;

-- To call the function and get the result
SELECT * FROM duplicate_entries(99, 100);

-- To see the updated 'foo' table
SELECT * FROM foo;
    old_id new_id
    1 6
    2 7
    3 8
    id foo_content condition_col
    1 Sing, O goddess 99
    2 the anger of Achilles 99
    3 son of Peleus 99
    4 that brought countless ills 2
    5 upon the Achaeans 3
    6 Sing, O goddess 99
    7 the anger of Achilles 99
    8 son of Peleus 99
    Login or Signup to reply.
  2. 0

    You could, but probably shouldn’t, rely on the order of inserted rows to match between the selection and the RETURNING clause. A better approach is to generate the new ids ahead of the insert, using the sequence that is underlying the identity column:

    CREATE OR REPLACE FUNCTION duplicate_entries(condition INT, new_condition INT)
RETURNS TABLE (old_id INT, new_id INT)
LANGUAGE SQL
BEGIN ATOMIC
  WITH mapping AS (
    SELECT id AS old_id, nextval(pg_get_serial_sequence('foo', 'id')) AS new_id
    FROM foo f
    WHERE f.condition_col = condition
  ), __insertion AS (
    INSERT INTO foo (id, foo_content, condition_col)
    OVERRIDING SYSTEM VALUE
    SELECT m.new_id, f.foo_content, new_condition
    FROM mapping m
    JOIN foo f ON m.old_id = f.id
  )
  TABLE mapping;
END;

    (online demo)

    This does however require USAGE permission on the underlying sequence, which may not be granted by default even if a role can normally use the identity column. You may need to use SECURITY DEFINER on the function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search