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:

Previous
From: Christopher Kings-Lynne
Date:
Subject: Re: OWNER TO on all objects
Next
From: Shachar Shemesh
Date:
Subject: Using domains for case insensitivity