By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

Unable to add Array of table data type to function as IN parameter!

P: 1
I want some expert help, like you.

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.

Expand|Select|Wrap|Line Numbers
  1. ----------------
  2.  
  3. CREATE TABLE book
  4. (
  5.   id smallint NOT NULL DEFAULT 0,       
  6.   bname text,       
  7.   btype text,
  8.   bprices numeric(11,2)[],
  9.   CONSTRAINT key PRIMARY KEY (id )
  10. );
  11.  
  12. --------------
  13.  
  14. CREATE OR REPLACE FUNCTION save_book(thebook book)
  15.   RETURNS text AS
  16. $BODY$
  17. DECLARE 
  18. myoutput text :='Nothing has occured';
  19. BEGIN
  20.  
  21.     update book set 
  22.     bname=thebook.bname,
  23.     btype=thebook.btype,bprices=thebook.bprices  WHERE id=thebook.id;
  24.  
  25.     IF FOUND THEN
  26.         myoutput:= 'Record with PK[' || thebook.id || '] successfully updated';
  27.         RETURN myoutput;
  28.     END IF;
  29.  
  30.     BEGIN
  31.         INSERT INTO book values(thebook.id,thebook.bname,thebook.btype,
  32.         thebook.bprices);
  33.         myoutput:= 'Record successfully added';           
  34.     END;
  35.  RETURN myoutput;
  36.  
  37.     END;
  38. $BODY$
  39.   LANGUAGE plpgsql VOLATILE
  40.   COST 100;
  41.  
  42.   -----------------------
  43.  
  44.   SELECT save_book(row(179,'the art of war','fiction1', array[190,220])::book); 
  45.  
  46.  
Problem: Here I can add only one row at a time, but I want to pass multiple rows to functions, I couldn't.
May 10 '16 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.