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?

Answer 1

Like this:

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

(Use appropriate numeric type instead of INTEGER).

Answer 2

Or simpler:

UPDATE albumphoto
SET    "order" = 1
WHERE  idtable = 1
AND    idx = split_part(text, ',', 1)::int  -- cast to actual type (if not a string type)
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 - at full cost. Rather do nothing instead. Related (see last paragraph):

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

And you don't need a variable.