Thread: understanding Datum -> char * -> Datum conversions
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, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr Radioactive cats have 18 half-lives.
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
somecode 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() tocreate 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)
pfree() after palloc() in trigger (was: Re: understanding Datum -> char * -> Datum conversions)
From
Louis-David Mitterrand
Date:
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, Pennsylvania19026
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
Re: pfree() after palloc() in trigger (was: Re: understanding Datum -> char * -> Datum conversions)
From
Tom Lane
Date:
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