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 !
