Re: [SQL] converting from number to text

>>>>> "fRS" == Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl> writes:

fRS> Hi!
fRS> I need convert number(float or integer) to text, but my version of
fRS> postgres (i.e. 6.3.2) seems to have not such a function?
fRS> Is it may be in newer versions or is there any work-around?
fRS> TIA
fRS> Rems
fRS> p.s. concept is to have field, which contain date in timestamp form
fRS> (number of seconds from 'epoch'), but in which I could insert also value
fRS> 'until cancellation' or any other text.

I send text of four function for int|float <-> text conversion.

==============================
Headers of the functions (you must replace DESTLIB to the real name of
your .so file):

void * text2float(text *t);
/*sql create function text2float(text) returns float as 'DESTLIB' language 'c'*/

text* float2text(float8 *f, int prec);
/*sql create function float2text(float, int) returns text as 'DESTLIB' language 'c'*/

int4 text2int(text* t);
/*sql create function text2int(text) returns int as 'DESTLIB' language 'c'*/

text* int2text(int4 i);
/*sql create function int2text(int) returns text as 'DESTLIB' language 'c'*/
===============================

Bodies of the functions:

void * text2float(text *t)
{
int l;
char *c;
float8 * ret;

if(!t)
return NULL;

l = VARSIZE(t) - VARHDRSZ + 1;
c = palloc(l);

memset(c, 0, l);
memcpy(c, VARDATA(t), l);
ret = palloc(sizeof(float8));
*ret = strtod(c, NULL);
return ret;
}

text* float2text(float8 *f, int prec)
{
char s[40];
char fmt[10];
text* ret;
int l;

if(!f)
return NULL;

sprintf(fmt, "%%.%dlf", prec);
sprintf(s, fmt, *f);

l = VARHDRSZ + strlen(s);
ret = palloc(l);
VARSIZE(ret) = l;
memcpy(VARDATA(ret), s, strlen(s));

return ret;
}

int4 text2int(text* t)
{
char* buf;
int l;

if (!t)
return 0;

l = VARSIZE(t) - VARHDRSZ;
buf = palloc(l + 1);
memset(buf, 0, l + 1);
memcpy(buf, VARDATA(t), l);

return atoi(buf);
}

text* int2text(int4 i)
{
text *ret;
char s[20];
int l;

sprintf(s, "%d", i);
l = VARHDRSZ + strlen(s);
ret = palloc(l);
VARSIZE(ret) = l;
memcpy(VARDATA(ret), s, l - VARHDRSZ);

return ret;
}

===============================

--
Anatoly K. Lasareff Email: tolik(at)icomm(dot)ru
Senior programmer