| 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: | Whole Thread | Raw Message | Download mbox | Resend email | 
| 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 !
| 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 |