Re: varchar and spaces problem..

Поиск
Список
Период
Сортировка
Искать

Re: varchar and spaces problem..

От:
trainee12@yeah.net
Дата:
don't store trail blank in varchar column.

You should recompile postgresql
There is only tiny change in "src/backend/utils/adt/varchar.c"
****************************************************************
Datum
varcharin(PG_FUNCTION_ARGS)
{
	char	   *s = PG_GETARG_CSTRING(0);

#ifdef NOT_USED
	Oid			typelem = PG_GETARG_OID(1);
#endif
	int32		atttypmod = PG_GETARG_INT32(2);
	VarChar    *result;
	size_t		len,
				maxlen;

	char	   *ermsg;

	len = strlen(s);

	if ((ermsg = pg_verifymbstr(s, len)))
		elog(ERROR, "%s", ermsg);

	maxlen = atttypmod - VARHDRSZ;

	if (atttypmod >= (int32) VARHDRSZ && len > maxlen)
	{
		/* Verify that extra characters are spaces, and clip them off */
		size_t		mbmaxlen = pg_mbcharcliplen(s, len, maxlen);

		if (strspn(s + mbmaxlen, " ") == len - mbmaxlen)
			len = mbmaxlen;
		else
			elog(ERROR, "value too long for type character varying(%d)",
				 (int) maxlen);
	}

/*    **** don't save  trail space char  */

	while (len > 0)
	{
		if (*(s + len - 1) != ' ')
			break;
		len--;
	}
/*  **** end                           */
        
	result = palloc(len + VARHDRSZ);
	VARATT_SIZEP(result) = len + VARHDRSZ;
	memcpy(VARDATA(result), s, len);

#ifdef CYR_RECODE
	convertstr(VARDATA(result), len, 0);
#endif

	PG_RETURN_VARCHAR_P(result);
}

********************************************************
After all, YOU should migrate you previous data!
UPDATE TABLE SET VARCHAR_COL=TRIM(VARCHAR_COL,' ') 

FAQ