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 !
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
