Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Your function looks a bit overcomplicated for me, but anways. An array literal must be enclosed by single quotes, so try the following: ave_book((179,the art of war,fiction,'{190,220}')::book. The constructed row does not need the quotes. –  dezso Feb 27 '13 at 11:59
    
when i run that i get the error ERROR: syntax error at or near "art" –  indago Feb 27 '13 at 12:04
1  
Individual string literals must still be enclosed in quotation marks. –  Andriy M Feb 27 '13 at 12:23
    
Sorry, the correct one is ave_book((179, 'the art of war', 'fiction', '{190,220}')::book, just as Andriy said. –  dezso Feb 27 '13 at 12:24
    
@dezso: Seems an answer to me. :) –  Andriy M Feb 27 '13 at 12:26
show 1 more comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
+1 for using an array constructor, escaping can be a nightmare –  Jack Douglas Feb 27 '13 at 12:42
    
@Chris, The first solution seems good and for me i think is the best! –  indago Feb 27 '13 at 12:48
    
@JackDouglas, one thing we are looking at doing is going the first route in LSMB specifically because we can use CSV frameworks that are already working. –  Chris Travers Feb 27 '13 at 12:54
add comment

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:
Casting issue when calling a function with composite type parameter

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I made that function to handle all inserts and updates shielding the application from directly dealing with the tables, do you think that having that function does not add any advantage? –  indago Feb 27 '13 at 12:43
    
My $0.02 on the design decision. I think it has a great advantage provided you have application code to look up the structure of the type involved and construct the argument for you. This gives you a discoverable API which is very helpful. Without that, however, it means that if your table structure changes you have extra places to make a change which is definitely not good. I say this being one who is moving my development in the direction you are going, and on the whole I think it is a good idea. It does have dangers though. –  Chris Travers Feb 27 '13 at 12:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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