Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE books_table (
  book_id numeric
, row_num numeric
, book_owner text
, book_owner_id text
);

CREATE OR REPLACE FUNCTION update_table(_books_arr books_table[])
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is not necessary
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

-- Simpler call with VARIADIC parameter
CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is no improvement
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

-- simple SQL function with unnest
CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
  RETURNS void
  LANGUAGE sql AS
$func$
   INSERT INTO books_table
   SELECT * FROM unnest(_books_arr) b;
$func$;

CREATE TABLE



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}');


update_table


Saved



SELECT 1


-- Simpler call with VARIADIC
SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');


update_table_variadic


Saved



SELECT 1


SELECT update_table_set('(3,100,Arthur,1)', '(3,50,Zaphod,1)');


update_table_set


null



SELECT 1


TABLE books_table;


book_id
row_num
book_owner
book_owner_id


1
100
Arthur
1

1
50
Zaphod
1

2
100
Arthur
1

2
50
Zaphod
1

3
100
Arthur
1

3
50
Zaphod
1



SELECT 6

book_id	row_num	book_owner	book_owner_id
1	100	Arthur	1
1	50	Zaphod	1
2	100	Arthur	1
2	50	Zaphod	1
3	100	Arthur	1
3	50	Zaphod	1