understanding Datum -> char * -> Datum conversions

On Wed, 24 May 2000, Louis-David Mitterrand wrote:

Hello,

I am learning to programm triggers in C by using the examples and the
programmer's manual but it's a steep learning curve for a mere perl
programmer ;-)

What I am trying to do for instance is:
- read a ::text colum with SPI_getbinval(),
- convert it to a char*,
- modify it,
- convert it back to a Datum,
- reinsert it into the tuple through SPI_modifytuple,

The conversions involve some pointer magic and casting that I really
don't grasp.

Also I am trying to read a timestamp with SPI_getbinval and get the
number of seconds contained. Using DatumGetInt32 doens't seem to do it.

Thanks in advance for your insight, cheers,

Examples:

* Add actual time to column "chtime":

Datum chtime = PointerGetDatum(timestamp_in("now"));
int attnum = SPI_fnumber(tupdesc, "chtime");

rettuple = SPI_modifytuple(CurrentTriggerData->tg_relation,
rettuple, 1, &attnum, &chtime, NULL);

You can use instead "now" SPI_getvalue() .... etc.

* A small complex example:

HeapTuple xxx_trigger()
{
TupleDesc tupdesc;
HeapTuple rettuple = NULL;
int attnum;
char *value;
Datum newdt;

if (!CurrentTriggerData)
elog(ERROR, "XXX: triggers are not initialized");

if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) {
rettuple = CurrentTriggerData->tg_newtuple;
else if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;
else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;

tupdesc = CurrentTriggerData->tg_relation->rd_att;

if ( SPI_connect() < 0)
elog(ERROR, "SPI_connect() fail... ");

attnum = SPI_fnumber(tupdesc, "ColumnName");
value = SPI_getvalue(rettuple, tupdesc, attnum);

/* --- add some code for 'value' ---*/

newdt = PointerGetDatum(value);

rettuple = SPI_modifytuple(CurrentTriggerData->tg_relation,
rettuple, 1, &attnum, &newdt, NULL);

SPI_finish();
CurrentTriggerData = NULL;
return(rettuple);
}

.......... it must works :-)

Karel

Louis-David Mitterrand <cunctator@apartia.ch> writes:

What I am trying to do for instance is:
- read a ::text colum with SPI_getbinval(),
- convert it to a char*,
- modify it,
- convert it back to a Datum,
- reinsert it into the tuple through SPI_modifytuple,

The conversions involve some pointer magic and casting that I really
don't grasp.

Casting doesn't do it. Use text_out() to produce a null-terminated C
string from a text Datum, and use text_in() to create a new text Datum
after you've modified the string.

Also I am trying to read a timestamp with SPI_getbinval and get the
number of seconds contained. Using DatumGetInt32 doens't seem to do it.

Timestamp is a double not an int ... and the datum is actually a pointer
to it.

regards, tom lane

Casting doesn't do it. Use text_out() to produce a null-terminated C
string from a text Datum, and use text_in() to create a new text Datum
after you've modified the string.

By the way, I know there are a bunch of macros for tranforming to and
from Datum, but whenever I use them I have to figure out again exactly
how to do it.

Is there some documentation on the set of macros and what they do (or
some other means of describing how one translates arguments and return
values between internal form and "useful" programming form)?

Thanks for your help.

Cheers,
Brook

Brook Milligan <brook@biology.nmsu.edu> writes:

Is there some documentation on the set of macros and what they do (or
some other means of describing how one translates arguments and return
values between internal form and "useful" programming form)?

Just the source code :-(. Want to write some?

regards, tom lane

On Wed, May 24, 2000 at 05:53:57PM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.ch> writes:

What I am trying to do for instance is:
- read a ::text colum with SPI_getbinval(),
- convert it to a char*,
- modify it,
- convert it back to a Datum,
- reinsert it into the tuple through SPI_modifytuple,

The conversions involve some pointer magic and casting that I really
don't grasp.

Casting doesn't do it. Use text_out() to produce a null-terminated C
string from a text Datum, and use text_in() to create a new text Datum
after you've modified the string.

I can't find these functions anywhere in the included .h files. Where
should I look?

Also I am trying to read a timestamp with SPI_getbinval and get the
number of seconds contained. Using DatumGetInt32 doens't seem to do it.

Timestamp is a double not an int ... and the datum is actually a pointer
to it.

But for example I am trying to read the result from a "SELECT
date_part('epoch', now())" which returns a number of seconds since the
epoch and I can't find a way to obtain that value through SPI_getbinval,
I have to retrieve it through SPI_getvalue and use atoi() to convert it.
I'd rather access directly the native type instead.

Which DatumGet* function should I use there?

Thanks,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

On Wed, May 24, 2000 at 06:34:48PM +0200, Karel Zak wrote:

Also I am trying to read a timestamp with SPI_getbinval and get the
number of seconds contained. Using DatumGetInt32 doens't seem to do it.

Examples:

* Add actual time to column "chtime":

Datum chtime = PointerGetDatum(timestamp_in("now"));
int attnum = SPI_fnumber(tupdesc, "chtime");

rettuple = SPI_modifytuple(CurrentTriggerData->tg_relation,
rettuple, 1, &attnum, &chtime, NULL);

Thanks for your example, the timestamp_in() function is really useful.
But how should I do it if I want to:
1) retrieve a timestamp Datum,
2) add a few days to it,
3) store it back in the tuple,

The problem is converting the Datum to a base C type in order to be able
to modify it.

in pgsql/contrib/spi/timetravel.c there is an example which modifies
date columns and uses DatumGetInt32 to convert them. But this is
confusing because (1) Tom Lane says that datetime columns are double and
one should use DatumGetPointer (how do I use the pointer after?) and (2)
DatumGetInt32 doesn't seem to return the number of seconds.

You can use instead "now" SPI_getvalue() .... etc.

* A small complex example:

HeapTuple xxx_trigger()
{
TupleDesc tupdesc;
HeapTuple rettuple = NULL;
int attnum;
char *value;
Datum newdt;

if (!CurrentTriggerData)
elog(ERROR, "XXX: triggers are not initialized");

if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) {
rettuple = CurrentTriggerData->tg_newtuple;
else if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;
else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;

tupdesc = CurrentTriggerData->tg_relation->rd_att;

if ( SPI_connect() < 0)
elog(ERROR, "SPI_connect() fail... ");

attnum = SPI_fnumber(tupdesc, "ColumnName");
value = SPI_getvalue(rettuple, tupdesc, attnum);

But you get a char * value here through SPI_getvalue()?

/* --- add some code for 'value' ---*/

newdt = PointerGetDatum(value);

This is enough to convert the char * back to a Datum?

rettuple = SPI_modifytuple(CurrentTriggerData->tg_relation,

.......... it must works :-)

Thanks for your examples, I'm slowly beginning to understand...

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

The problem is converting the Datum to a base C type in order to be able
to modify it.

in pgsql/contrib/spi/timetravel.c there is an example which modifies
date columns and uses DatumGetInt32 to convert them. But this is
confusing because (1) Tom Lane says that datetime columns are double and
one should use DatumGetPointer (how do I use the pointer after?) and (2)
DatumGetInt32 doesn't seem to return the number of seconds.

See in PG's backend source files:

c.h - for datetype definition and Datum macros,
we have Datum macros for double/float types too.

buildin.h - for datetype conversion.
utils/timestamp.h ...etc.

and directory utils/adt for inspiration "how work
with pg types :-)

I believe that you will understand.

Karel

On Thu, May 25, 2000 at 12:03:55PM +0200, Karel Zak wrote:

The problem is converting the Datum to a base C type in order to be able
to modify it.

in pgsql/contrib/spi/timetravel.c there is an example which modifies
date columns and uses DatumGetInt32 to convert them. But this is
confusing because (1) Tom Lane says that datetime columns are double and
one should use DatumGetPointer (how do I use the pointer after?) and (2)
DatumGetInt32 doesn't seem to return the number of seconds.

See in PG's backend source files:

c.h - for datetype definition and Datum macros,
we have Datum macros for double/float types too.

buildin.h - for datetype conversion.
utils/timestamp.h ...etc.

and directory utils/adt for inspiration "how work
with pg types :-)

I'm reading these files but still got a problem:

Datum price_datum;
float new_price;

new_price = 10.5;
price_datum = Float32GetDatum(&new_price);

SPI_modifytuple(relation, tupdesc, &attnum, &price_datum, NULL);

... and when I check the DB the new_price field contains a negative
number, even though elog(NOTICE, ..., new_price) displays the correct
value.

If I could just understand how to correctly insert new_price it would
really help a great deal in understanding.

Thanks again,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

There are three types of people in the world: those who can count,
and those who can't.

See in PG's backend source files:

c.h - for datetype definition and Datum macros,
we have Datum macros for double/float types too.

buildin.h - for datetype conversion.
utils/timestamp.h ...etc.

and directory utils/adt for inspiration "how work
with pg types :-)

I'm reading these files but still got a problem:

float32 result = (float32) palloc(sizeof(float32data));

*result = 10.5;
SPI_modifytuple(relation, tupdesc, &attnum, Float32GetDatum(result),
NULL);

Right?

Karel

On Thu, May 25, 2000 at 12:51:52PM +0200, Karel Zak wrote:

I'm reading these files but still got a problem:

float32 result = (float32) palloc(sizeof(float32data));

*result = 10.5;
SPI_modifytuple(relation, tupdesc, &attnum, Float32GetDatum(result),
NULL);
Right?

Yes! It works now. My error was using a float32 instead of a float64, as
the internal type is really a float8. The confusion comes from defining
my tables with the type "float" which apparently defaults to float8.

Many thanks,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

"2c98611832ea3f6f5fdda95d3704fbb8" (a truly random sig)

On Thu, May 25, 2000 at 01:25:19PM +0200, Louis-David Mitterrand wrote:

On Thu, May 25, 2000 at 12:51:52PM +0200, Karel Zak wrote:

float32 result = (float32) palloc(sizeof(float32data));

SHould I pfree(result) before the end of the trigger function?

*result = 10.5;
SPI_modifytuple(relation, tupdesc, &attnum, Float32GetDatum(result),
NULL);

Instead of :

float64 result = (float64) palloc(sizeof(float64data));
SPI_modifytuple(relation, tupdesc, &attnum,Float32GetDatum(result),NULL);

Can I do

double result = 10.5; /* for example */
SPI_modifytuple(relation, tupdesc, &attnum,Float32GetDatum(&result),NULL);
^^^

ie: pass the address of (regular double) "result" instead of using a
pointer;

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

I don't build computers, I'm a cooling engineer.
-- Seymour Cray, founder of Cray Inc.

On Thu, May 25, 2000 at 12:51:52PM +0200, Karel Zak wrote:

I'm reading these files but still got a problem:

float32 result = (float32) palloc(sizeof(float32data));

*result = 10.5;
SPI_modifytuple(relation, tupdesc, &attnum, Float32GetDatum(result),
NULL);
Right?

Yes! It works now. My error was using a float32 instead of a float64, as
the internal type is really a float8. The confusion comes from defining
my tables with the type "float" which apparently defaults to float8.

That was my fault. I told you on IRC that float(float8) was float32,
and that float4 was float16. In fact float(float8) is float64, and
float4 is float32.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Casting doesn't do it. Use text_out() to produce a null-terminated C
string from a text Datum, and use text_in() to create a new text Datum
after you've modified the string.

I can't find these functions anywhere in the included .h files. Where
should I look?

Mea culpa, they're spelled "textout" and "textin". See
utils/builtins.h.

regards, tom lane

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Can I do

double result = 10.5; /* for example */
SPI_modifytuple(relation, tupdesc, &attnum,Float32GetDatum(&result),NULL);
^^^

I think you could get away with that in this example. The critical
question of course is whether the Datum pointer will continue to
be used after your routine exits. But SPI_modifytuple should have
created the new tuple (and copied the values of pass-by-reference
items, such as float8s, into it) before returning.

BTW you should be using Float64GetDatum. There's no real difference
in those two macros at the moment, but it's a type error that might
bite you someday (like as soon as you need to convert this code to
the new fmgr ;-)).

regards, tom lane