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.
ave_book((179,the art of war,fiction,'{190,220}')::book
. The constructed row does not need the quotes.ERROR: syntax error at or near "art"
ave_book((179, 'the art of war', 'fiction', '{190,220}')::book
, just as Andriy said.