SPI question

From: Darko Prenosil <darko(dot)prenosil(at)finteh(dot)hr>
To: pgsql-hackers(at)postgresql(dot)org
Subject: SPI question
Date: 2004-06-17 12:47:15
Message-ID: 200406171447.15808.darko.prenosil@finteh.hr
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: 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 !

Browse pgsql-hackers by date

  From Date Subject
Next Message Shachar Shemesh 2004-06-17 13:11:08 Using domains for case insensitivity
Previous Message Christopher Kings-Lynne 2004-06-17 08:38:27 Re: OWNER TO on all objects