Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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 !


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group