Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D1E4B9FA16B for ; Wed, 6 Dec 2006 08:30:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 85170-02 for ; Wed, 6 Dec 2006 08:30:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.231]) by postgresql.org (Postfix) with ESMTP id A76719F9F66 for ; Wed, 6 Dec 2006 08:30:14 -0400 (AST) Received: by qb-out-0506.google.com with SMTP id e21so1997515qba for ; Wed, 06 Dec 2006 04:30:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=lP7TdSXGOPb0nzBAH+TvtnKdzOaQbZmL9HNHpDaiPQAOZ0vvhP3rK2ydELkfeYemrewD6pcPO87pdNyygyWfkDJJhTXwYbo4EeAO+KjOyKzwZuLiXvKEYKIIf3mnpCTR04Ow14PZ39BkGLAm+W9T1QtnZWVmKmDSPZEls1rYOZk= Received: by 10.35.119.11 with SMTP id w11mr1263739pym.1165408213218; Wed, 06 Dec 2006 04:30:13 -0800 (PST) Received: by 10.35.80.8 with HTTP; Wed, 6 Dec 2006 04:30:13 -0800 (PST) Message-ID: Date: Wed, 6 Dec 2006 18:00:13 +0530 From: "Rajesh Kumar Mallah" To: pgsql-sql@postgresql.org Subject: Proper way of iterating over the column names in a trigger function. MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/67 X-Sequence-Number: 27023 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 ------------------------------------------------