pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProper way of iterating over the column names in a trigger function.
3+ messages / 2 participants
[nested] [flat]
* Proper way of iterating over the column names in a trigger function.
@ 2006-12-06 12:30 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-06 15:02 ` Re: Proper way of iterating over the column names in a trigger function. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-06 12:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am trying to interate over column names of a table on which a C trigger
function is called on UPDATE/DELETE and INSERT. SPI function
char * SPI_fname(TupleDesc rowdesc, int colnumber)
is being used. looks like the function is returning column names like
"........pg.dropped.2........" for deleted colnumbers. My question is
what is the proper way for iterating over column names of a table using
SPI_* functions.
(sorry for bad english)
SQL transcript:
-----------------------
tradein_clients=> UPDATE public.test set x=10;
NOTICE: no of column : 2
NOTICE: colname: x
NOTICE: colname: y
UPDATE 1
tradein_clients=> ALTER TABLE public.test add z int;
ALTER TABLE
tradein_clients=> UPDATE public.test set x=10;
NOTICE: no of column : 3
NOTICE: colname: x
NOTICE: colname: y
NOTICE: colname: z
UPDATE 1
tradein_clients=> ALTER TABLE public.test DROP y;
ALTER TABLE
tradein_clients=> UPDATE public.test set x=10;
NOTICE: no of column : 2
NOTICE: colname: x
NOTICE: colname: ........pg.dropped.2........
UPDATE 1
tradein_clients=>
i am pasting the minial code based on
http://www.postgresql.org/docs/8.1/static/trigger-example.html
--------------------------------------------------------------------------------------------------
#include "postgres.h"
#include "executor/spi.h"
#include "commands/trigger.h"
extern Datum trigf(PG_FUNCTION_ARGS);
static int get_no_columns(char *table_name, char *table_nspname);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char
*source_table,*source_table_nspname , *col_name ;
int ret,ncolumns, i ;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
tupdesc = trigdata->tg_relation->rd_att;
/* connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(INFO, "trigf : SPI_connect returned %d", ret);
source_table = SPI_getrelname(trigdata->tg_relation);
source_table_nspname = SPI_getnspname(trigdata->tg_relation);
ncolumns = get_no_columns( source_table,source_table_nspname );
elog(NOTICE, "no of column : %d" , ncolumns);
for (i = 1; i <= ncolumns ; i++)
{
col_name = SPI_fname(tupdesc, i);
elog (NOTICE , "colname: %s" , col_name);
}
SPI_finish();
return PointerGetDatum(rettuple);
}
static int
get_no_columns(char *table_name,char *table_nspname )
{
char query[512];
int ret;
int ncolumns = -1;
snprintf(query, 511,
"SELECT COUNT(pg_attribute.attname) AS a FROM
pg_class, pg_attribute , pg_namespace WHERE pg_class.relname='%s' and
pg_namespace.nspname='%s' AND pg_attribute.attnum > 0 AND
pg_attribute.attrelid=pg_class.oid and
pg_class.relnamespace=pg_namespace.oid and attisdropped is false" ,
table_name,
table_nspname
);
if ((ret = SPI_exec(query, 0)) < 0)
{
elog(ERROR, "get_no_columnss: could not get number of columns from
relation %s.%s ret: %d",
table_nspname , table_name , ret );
}
if (SPI_processed > 0)
{
/* this is a old code which is not using DatumGetInt64 , sorry for that :(
if its a mistake. */
ncolumns =
DatumGetInt32(DirectFunctionCall1
(int4in,
CStringGetDatum(SPI_getvalue
(SPI_tuptable->
vals[0], SPI_tuptable->tupdesc, 1))));
if (ncolumns < 1)
{
elog(ERROR, "get_no_columns: relation %s.%s does not exist",
table_nspname, table_name);
}
}
else
{
elog(ERROR,
"get_no_columns: could not get number columns in relation %s.%s",
table_nspname , table_name);
}
return (ncolumns);
}
------------------------------ end of code
------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Proper way of iterating over the column names in a trigger function.
2006-12-06 12:30 Proper way of iterating over the column names in a trigger function. Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2006-12-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-12-06 15:02 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> what is the proper way for iterating over column names of a table using
> SPI_* functions.
You need to pay attention to the attisdropped field of the TupleDesc
entries.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Proper way of iterating over the column names in a trigger function. [ SOLVED]
@ 2006-12-06 17:39 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-06 17:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 12/6/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> > what is the proper way for iterating over column names of a table using
> > SPI_* functions.
>
> You need to pay attention to the attisdropped field of the TupleDesc
> entries.
thanks.
did the below (hopefully gotcha free)
for (i = 1; i <= tupdesc->natts ; i++)
{
if ( tupdesc->attrs[i-1]->attisdropped)
continue;
col_name = SPI_fname(tupdesc, i);
elog (NOTICE , "colname: %s" , col_name);
}
Warm Regds
Mallah.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-12-06 17:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-06 12:30 Proper way of iterating over the column names in a trigger function. Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-06 17:39 Re: Proper way of iterating over the column names in a trigger function. [ SOLVED] Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox