4

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.

This is my type:

CREATE TYPE Books AS (
V_Book_ID NUMERIC,
V_Row_Num NUMERIC,
V_Book_OWNER TEXT,
V_Book_OWNER_ID TEXT
);

This is my function:

CREATE OR REPLACE FUNCTION Update_Table(row_book Books[]) RETURNS TEXT AS $$
DECLARE
   Status TEXT;
   I_Max integer := array_length(row_book, 1);
BEGIN
FOR I in 1..I_Max
  LOOP
   INSERT INTO books_table(Book_ID,
   Row_Num,
   Book_OWNER,
   Book_OWNER_ID)
   values
   (row_book[I].V_Book_ID,
   row_book[I].V_Row_Num,
   row_book[I].V_Book_OWNER,
   row_book[I].V_Book_OWNER_ID);
END LOOP;

   STATUS:='Saved';
exception when others then
   STATUS:='failure';
   RETURN STATUS;

END;
$$ language plpgsql;

How do I send data to the function or how should I call the function with data?

7

I answered a basically identical question on SO some time ago:

Suggesting the same solution with unnest() like @a_horse did.

Assuming that books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table:

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

PL/pgSQL function

If you need the plpgsql function for some undeclared reason consider this:

CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT 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$ LANGUAGE plpgsql;

Call (with list of row values!):

SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

Without using VARIADIC, the function call would require a single array as a whole. Either an array literal (optionally with explicit cast):

SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

Or you can use an array constructor like @a_horse demonstrates. Array literal are often easier to provide.

Major points:

SQL function

If you don't need to catch the exception, and you also don't need the string 'Saved' / 'Failure' to be returned, simplify:

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

SQL Fiddle.

2

To create an array of a type, use an explicit array constructor:

array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[]

So to call your function you would need to use:

select update_table(array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[])

But your function has an error: after the loop you are missing a return statement, because the one in the exception block is only executed if an exception occurs.

So you need something like this:

begin 

  .... 

  STATUS:='Saved';
  return status;  ---<<< this is missing

  exception when others then
    STATUS:='failure';
    RETURN STATUS; --<<< this is only execute if an exception occurs

END;

Alternatively you need another begin .. end; block:

begin 

  begin    
    for ... 
    end loop;

    STATUS:='Saved';

  exception when others then
    STATUS:='failure';
  end;

  RETURN STATUS; 
END;

Unrelated, but: you don't need the loop to iterate over the array. You can do that more efficiently using a single statement:

INSERT INTO books_table 
   (Book_ID, Row_Num, Book_OWNER, Book_OWNER_ID)
select *
from unnest(row_book);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.