[SQL] Proper way of iterating over the column names in a trigger function.
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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Count field in query
Hi all.
Is there any way to build a query with a field that has the IndexCount
of the query.
It's something like the number of the row returned (starting with 1).
Something like:
select * from mytable order by name;
id | name | CountField
7 | KK | 1
98 | LL | 2
5 | ZZ | 3
select * from mytable order by id;
id | name| CountField
5 | ZZ | 1
7 | KK | 2
98 | LL | 3
I was thinking to create a sequence:
create temporary sequence MYSEQUENCE increment 1 MINVALUE 1;
And put this sequence as field "nextval('mysequence')":
select *,nextval('mysequence') from mytable;
But I think its not the best way to do this! Couse I need to set
sequence value to 1 everytime.
Can Someone help me?
Thanks
Lucas Vendramin (Brazil)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Proper way of iterating over the column names in a trigger function.
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Query is fast and function is slow
The query
select count(*) from documents where doc_num = '106973821' and (select
bit_or(group_access) from mda_groups where group_name in (select groname
from pg_user,pg_group where usename = 'bbob' and usesysid = any(grolist)
and (groname ~ '.*owner$' or groname = 'admin'))) & access >
'0'::bit(100);
returns very fast
If I create function
create or replace function check_for_update_permission(text,text) returns
boolean as '
declare
doc_number alias for $1;
user alias for $2;
doc_count integer;
begin
select count(*) into doc_count from documents where doc_num = doc_number
and (select bit_or(group_access) from mda_groups where group_name in
(select groname from pg_user,pg_group where usename = user and usesysid =
any(grolist) and (groname ~ ''.*owner$'' or groname = ''admin''))) &
access > ''0''::bit(100);
if doc_count > 0 then
return(true);
end if;
return(false);
end;
' language 'plpgsql';
and run "select check_for_update_permission('106973821','bbob');"
it returns the correct info but takes several minutes
Would someone please enlighten me.
Can you do something like explain analyze on a function
Thanks
Richard
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Proper way of iterating over the column names in a trigger function. [ SOLVED]
On 12/6/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] INSERT DELETE RETURNING
Hi All, As per Daniel Caune's posting (with no replies) on October 22nd, (http://archives.postgresql.org/pgsql-sql/2006-10/msg00195.php), Can I do this.? INSERT INTO stock_deleted_tmp ( code, description, date_deleted ) DELETE FROM ONLY stock_tmp WHERE grp = '0001' RETURNING code, description, current_timestamp At the moment I'm getting this error: ERROR: syntax error at or near "DELETE" LINE 6: DELETE FROM ONLY stock_tmp Yes I have upgraded to PG 8.2: horseland=# DELETE FROM ONLY stock_tmp WHERE grp = '0001' RETURNING code, description, current_timestamp; code | description | now +--+ --- 920687 | DEBRISOL 500ML | 2006-12-07 09:57:37.434605+11 460950 | ROMA BASIC BUZZ OFF F/MSK SND/BRN S | 2006-12-07 09:57:37.434605+11 460951 | ROMA BASIC BUZZ OFF F/MSK SND/BRN M | 2006-12-07 09:57:37.434605+11 460952 | ROMA BASIC BUZZ OFF F/MSK SND/BRN L | 2006-12-07 09:57:37.434605+11 460953 | ROMA BASIC BUZZ OFF F/MSK SND/BRN XL | 2006-12-07 09:57:37.434605+11 402832 | COMB PLASTIC BALL BLK| 2006-12-07 09:57:37.434605+11 (6 rows) DELETE 6 horseland=# Thanks, -p Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, VIC, 3131 AUSTRALIA ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
