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.

Wed, 24 May 2000 18:26:41 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
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





Wed, 24 May 2000 18:34:48 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: understanding Datum -> char * -> Datum conversions
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

Wed, 24 May 2000 17:53:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: understanding Datum -> char * -> Datum conversions 
   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

Wed, 24 May 2000 16:29:22 -0600 (MDT)
Brook Milligan <brook@biology.nmsu.edu>
Re: understanding Datum -> char * -> Datum conversions
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

Wed, 24 May 2000 19:00:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: understanding Datum -> char * -> Datum conversions 
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

Thu, 25 May 2000 11:40:56 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
Re: understanding Datum -> char * -> Datum conversions
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

Thu, 25 May 2000 11:53:54 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
Re: understanding Datum -> char * -> Datum conversions
> 
> 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


Thu, 25 May 2000 12:03:55 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: understanding Datum -> char * -> Datum conversions
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.

Thu, 25 May 2000 12:44:40 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
Re: understanding Datum -> char * -> Datum conversions

> >  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


Thu, 25 May 2000 12:51:52 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: understanding Datum -> char * -> Datum conversions
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)

Thu, 25 May 2000 13:25:19 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
Re: understanding Datum -> char * -> Datum conversions
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. 

Thu, 25 May 2000 14:40:07 +0200
Louis-David Mitterrand <cunctator@apartia.ch>
pfree() after palloc() in trigger (was: Re: understanding Datum ->
	char * -> Datum conversions)
> 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

Thu, 25 May 2000 09:42:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: understanding Datum -> char * -> Datum conversions
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

Thu, 25 May 2000 10:55:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: understanding Datum -> char * -> Datum conversions 
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

Thu, 25 May 2000 11:11:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pfree() after palloc() in trigger (was: Re: understanding Datum
	-> char * -> Datum conversions)