How do I convert a character to integer within a PostgreSQL (9.1) function?

Question

I have this following code:

BEGIN
   x := split_part(text, ',', 1);
   UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = x;   
END

But my column table named idx is a numeric type, and the split_part returns a character type to the variable x. I've tried using CAST, but I don't know how to use it properly.

Any ideas?

Solution 1

Like this:

UPDATE albumphoto SET order = 1 WHERE idtable = 1 AND idx = CAST (x AS INTEGER);

(Use appropriate numeric type instead of INTEGER).

Solution 2

Or simpler:

UPDATE albumphoto
SET    order = 1
WHERE  idtable = 1
AND    idx = split_part(text, ',', 1)::int  -- or whatever type it is
AND    order IS DISTINCT FROM 1;

expression::type is the simple (non-SQL-standard) Postgres way to cast. Details in the manual in the chapter Type Casts.
More about data types in PostgreSQL.

The last predicate I added is useful if order could already be 1, in which case the update wouldn't change anything, but still cost the same. Rather do nothing instead. Related (consider the last paragraph):

• How do I (or can I) SELECT DISTINCT on multiple columns?

And you don't need a variable here.