How to add array of table data type to function as IN parameter?

Question

I want to add more than one row into table using function, but unable to pass F1(thebook book[]) as. Kindly assist if any solution.

CREATE TABLE book
(
  id smallint NOT NULL DEFAULT 0,       
  bname text,       
  btype text,
  bprices numeric(11,2)[],
  CONSTRAINT key PRIMARY KEY (id )
);

CREATE OR REPLACE FUNCTION save_book(thebook book)
  RETURNS text AS
  $BODY$
DECLARE 
  myoutput text :='Nothing has occured';
BEGIN

  update book set 
  bname=thebook.bname,
  btype=thebook.btype,bprices=thebook.bprices  WHERE id=thebook.id;

  IF FOUND THEN
    myoutput:= 'Record with PK[' || thebook.id || '] successfully updated';
    RETURN myoutput;
  END IF;

  BEGIN
    INSERT INTO book values(thebook.id,thebook.bname,thebook.btype, thebook.bprices);
    myoutput:= 'Record successfully added';           
  END;
  RETURN myoutput;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

SELECT save_book(row(111,'the art of living','ABC', array[190,220])::book);

So do you have your answer? – Erwin Brandstetter May 15 '16 at 02:59 — Erwin Brandstetter, May 15 '16 at 02:59

score 0 · Answer 1 · edited May 23 '17 at 12:07

For up to 100 rows, use a VARIADIC parameter for convenience. This way you can list individual rows and don't have figure out the complex syntax for an array of rows. The parameter is still visible as array inside the function. Details:
- Return rows matching elements of input array in plpgsql function
- PL/pgSQL Array of Rows
Use the new UPSERT implementation in Postgres 9.5+ INSERT ... ON CONFLICT UPDATE:
Unnest the array with unnest()

CREATE OR REPLACE FUNCTION save_book(VARIADIC thebook book[])
  RETURNS void AS
$func$
BEGIN

INSERT INTO book(id, bname, btype, bprices)  -- spell out target columns!
SELECT *
FROM   unnest(thebook) t
ON     CONFLICT (id) DO UPDATE
SET    bname = excluded.bname                -- excluded key word!
     , btype = excluded.btype
     , bprices = excluded.bprices;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT save_book('(111,"the art of living",ABCX, "{190,220}")'
               , '(112,"the art of living",ABCY, "{190,220}")');

How to add array of table data type to function as IN parameter?

1 Answers1