How to pass a table type with an array field to a function in postgresql

Question

i have a table called book

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

and a function save_book

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;

now when i call the function

SELECT save_book('(179,the art of war,fiction,{190,220})'::book);

i get the error

ERROR: malformed array literal: "{190"
SQL state: 22P02
Character: 18

i dont understand because i dont see any error in the format of the array, any help?

Chris Travers · Accepted Answer · 2013-02-27T12:48:46.530

This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so:

SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book);

should work. In essence a neat trick is creating a csv and enclosing in tuple or array identifiers. The big issue is that you have to deal with escaping (doubling quotes at every level as needed). So the following is exactly equivalent:

SELECT save_book('(179,"the art of war","fiction","{""190"",""220""}")'::book);

The second approach is to use a row constructor:

SELECT save_book(row(179,'the art of war','fiction', array[190,220])::book);

The first solution has the obvious advantage of being able to take advantage of existing programming frameworks for CSV generation and escaping. The second is cleanest in SQL. They can be mixed and matched.

score 6 · Answer 2 · edited Jun 15 '20 at 09:05

If you ever wonder about the correct syntax for a row type, ask Postgres. It should know:

SELECT b FROM book b LIMIT 1;  -- or: WHERE id = 179;

Which will return a text representation of your row in valid format:

(179,"the art of war",fiction,"{190,220}")

Values of columns are represented as an unquoted, comma-separated list, enclosed in paretheses.
Double quotes are use around values, if there can be ambiguity - including text with white space. While in this particular case the double-quotes around "the art of war" are optional, the double-quotes around "{190,220}" are necessary for an array.

Enclose the string in single quotes, modify and test:

SELECT '(333,the art of war,fiction,"{191,220,235}")'::book

Function reviewed

Consider what we discussed under the related, preceding question:
Issue with composite type in an UPSERT function

A separate block (BEGIN .. END;) is only useful if you want to catch the EXCEPTION an INSERT might raise. Since a block with exception carries some overhead, it makes sense to have a separate block that might never be entered:

CREATE OR REPLACE FUNCTION save_book(thebook book)
  RETURNS text AS
$BODY$
BEGIN
   UPDATE book
   SET    bname =   thebook.bname
         ,btype =   thebook.btype
         ,bprices = thebook.bprices
   WHERE  id = thebook.id;
IF FOUND THEN
      RETURN format('Record with PK[%s] successfully updated', thebook.id);
   END IF;
BEGIN
      INSERT INTO book SELECT (thebook).*;
      RETURN format('Record with PK[%s] successfully inserted', thebook.id);
EXCEPTION WHEN unique_violation THEN
      UPDATE book
      SET    bname =   thebook.bname
            ,btype =   thebook.btype
            ,bprices = thebook.bprices
      WHERE  id = thebook.id;
   END;
RETURN format('Record with PK[%s] successfully updated', thebook.id);
END
$BODY$  LANGUAGE plpgsql

Else, simplify:

CREATE OR REPLACE FUNCTION save_book(thebook book)
  RETURNS text AS
$BODY$
BEGIN
   UPDATE book
   SET    bname =   thebook.bname
         ,btype =   thebook.btype
         ,bprices = thebook.bprices
   WHERE  id = thebook.id;
IF FOUND THEN
      RETURN format('Record with PK[%s] successfully updated', thebook.id);
   END IF;
INSERT INTO book SELECT (thebook).*;
   RETURN format('Record with PK[%s] successfully inserted', thebook.id);
END
$BODY$  LANGUAGE plpgsql

I also simplified your INSERT statement. It's safe to omit the column list from INSERT under the given circumstances.

score 3 · Answer 3 · answered Feb 27 '13 at 12:35

While I don't see the real advantage of your solution, I mean passing a row to the function instead of passing the individual values as in

CREATE OR REPLACE FUNCTION save_book2(
      integer
    , text
    , text
    , integer[]
)
RETURNS text AS
...

Anyway, your solution works as well if you call the function correctly:

SELECT ave_book((179, 'the art of war', 'fiction', '{190,220}')::book);

That is, the record expression doesn't need quoting, while the text values and the array literal do.

How to pass a table type with an array field to a function in postgresql

3 Answers3

Function reviewed