SPI question - Mailing list pgsql-hackers
From | Darko Prenosil |
---|---|
Subject | SPI question |
Date | |
Msg-id | 200406171447.15808.darko.prenosil@finteh.hr Whole thread Raw |
List | pgsql-hackers |
I'm describing view using the following SPI function. All is fine except "attrelid" is everywhere set to 0. What I'm doing wrong ? How can I get table oid and column id for every column in SQL result ? PG_FUNCTION_INFO_V1(check_view); Datum check_view(PG_FUNCTION_ARGS) { int spiRet=0;PQExpBuffer queryBuff = createPQExpBuffer(); char *schemaName = GET_STR(PG_GETARG_TEXT_P(0)); char *viewName = GET_STR(PG_GETARG_TEXT_P(1));int ret = -1; if (schemaName == NULL) elog(ERROR, "schemaName not set"); if (viewName == NULL) elog(ERROR, "viewName not set"); if ((spiRet = SPI_connect()) < 0) elog(ERROR, "rlog: SPI_connect returned %d", spiRet); printfPQExpBuffer(queryBuff, "SELECT definition" " FROM pg_views WHERE schemaname='%s' " " AND viewname='%s';",schemaName,viewName); ret = SPI_exec(queryBuff->data,1);//elog(NOTICE, "%s",queryBuff->data); if (ret == SPI_OK_SELECT){ if ( SPI_processed > 0 ){ TupleDesc tupdesc = SPI_tuptable->tupdesc; printfPQExpBuffer( queryBuff, "%s", SPI_getvalue(SPI_tuptable->vals[0],tupdesc,1) ); }else{ elog(ERROR, "Unexisting view %s.%s", schemaName,viewName); }}else{ elog(ERROR, "Error executing %s", queryBuff->data );} //elog(NOTICE, "%s", queryBuff->data );ret = SPI_exec(queryBuff->data,1);if (ret < 0){ elog(ERROR, "Error executing%s", queryBuff->data );}else{ int i=0; TupleDesc tupdesc = SPI_tuptable->tupdesc; elog(NOTICE,"************************************"); elog(NOTICE,"View %s.%s - column count:% i",schemaName,viewName,tupdesc->natts); for (i=0; i < tupdesc->natts; i++){ elog(NOTICE," colname %s", tupdesc->attrs[i]->attname.data); elog(NOTICE,"-----------------"); elog(NOTICE," attrelid %i", (int)tupdesc->attrs[i]->attrelid); elog(NOTICE," atttypid %i", tupdesc->attrs[i]->atttypid); elog(NOTICE," attlen %i", tupdesc->attrs[i]->attlen); elog(NOTICE," attnum %i", tupdesc->attrs[i]->attnum); elog(NOTICE," attstattarget %i", tupdesc->attrs[i]->attstattarget); elog(NOTICE," attndims %i", tupdesc->attrs[i]->attndims); elog(NOTICE," attcacheoff %i", tupdesc->attrs[i]->attcacheoff); elog(NOTICE," atttypmod %i", tupdesc->attrs[i]->atttypmod); elog(NOTICE," attbyval %i", tupdesc->attrs[i]->attbyval); elog(NOTICE," attstorage %i", tupdesc->attrs[i]->attstorage); elog(NOTICE," attisset %i", tupdesc->attrs[i]->attisset); elog(NOTICE," attalign %i", tupdesc->attrs[i]->attalign); elog(NOTICE," attnotnull %i", tupdesc->attrs[i]->attnotnull); elog(NOTICE," atthasdef %i", tupdesc->attrs[i]->atthasdef); elog(NOTICE," attisdropped %i", tupdesc->attrs[i]->attisdropped); elog(NOTICE," attislocal %i", tupdesc->attrs[i]->attislocal); elog(NOTICE," attinhcount %i", tupdesc->attrs[i]->attinhcount); }}PG_RETURN_BOOL(true); } CREATE OR REPLACE FUNCTION check_view (text,text) RETURNS bool AS '/usr/local/pgsql/lib/libplpq.so','check_view' LANGUAGE'c' WITH (isstrict); SELECT check_view('pg_catalog','pg_tables'); Here is the result (not that attrelid is 0 for all cols): NOTICE: ************************************ NOTICE: View pg_catalog.pg_tables - column count:6 NOTICE: colname schemaname NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 19 NOTICE: attlen 64 NOTICE: attnum 1 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 0 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 105 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 NOTICE: colname tablename NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 19 NOTICE: attlen 64 NOTICE: attnum 2 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 0 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 105 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 NOTICE: colname tableowner NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 19 NOTICE: attlen 64 NOTICE: attnum 3 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 0 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 105 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 NOTICE: colname hasindexes NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 16 NOTICE: attlen 1 NOTICE: attnum 4 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 1 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 99 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 NOTICE: colname hasrules NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 16 NOTICE: attlen 1 NOTICE: attnum 5 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 1 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 99 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 NOTICE: colname hastriggers NOTICE: ----------------- NOTICE: attrelid 0 NOTICE: atttypid 16 NOTICE: attlen 1 NOTICE: attnum 6 NOTICE: attstattarget -1 NOTICE: attndims 0 NOTICE: attcacheoff -1 NOTICE: atttypmod -1 NOTICE: attbyval 1 NOTICE: attstorage 112 NOTICE: attisset 0 NOTICE: attalign 99 NOTICE: attnotnull 0 NOTICE: atthasdef 0 NOTICE: attisdropped 0 NOTICE: attislocal 1 NOTICE: attinhcount 0 Regards !
pgsql-hackers by date: