Преобразование varchar в integer в Postgres на лету.

Для подобного преобразования достаточно определить следующую функцию:

	db=> CREATE FUNCTION toint(varchar)
	RETURNS integer
	STRICT IMMUTABLE LANGUAGE SQL AS
	'SELECT cast($1 as integer);';
	db=> \d public.items
	Column | Type
	-----------------------+------------------------
	...
	description | character varying(255)
	...
	lastvalue | character varying(255)
	...
	db=> select sum(lastvalue) from public.items where description like '%YOUR ITEM%' and lastvalue is not null;
	ERROR: function sum(character varying) does not exist
	LINE 1: select sum(lastvalue) from public.items where description li...
	db=> select sum(toint(lastvalue)) from public.items where description like '%YOUR ITEM%' and lastvalue is not null;
	sum
	-------
	100000
	(1 row)

view raw gistfile1.sql hosted with ❤ by GitHub

Источник решения: stackoverflow.com.