[Gambas-user] Postgres : Connection.Tables

[Bruce]

On 31/10/20 10:37 pm, Benoît Minisini wrote:
> Le 31/10/2020 à 05:05, Bruce a écrit :
>> The Connection.Tables property returns a "a virtual collection used 
>> for managing the tables of the database". However this collection 
>> includes tables, views and materialised views. The 
>> .Connection.Tables["name"].Type property is always empty ("") so
>> is there any way to tell if a Table is a real table or a view?
>>
>> tia
>> bruce
>>
>> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
> 
> Not at the moment.
> 

OK, well here is what I've developed. (Also attached as a text file)
---------------------------
/*****************************************************************************

	table_type()

	Returns or sets the table type.

	<db> is the database handle.
	<table> is the table name.
	<newtype> is the new type

	This function returns the table type.

	NOTE: Postgresql does not allow setting the table type under normal 
circumstances.

*****************************************************************************/

static char *table_type(DB_DATABASE *db, const char *table, const char 
*newtype)
{
	if (newtype)
	{
		GB.Error("PostgreSQL does not allow setting the table types");

		return NULL;
	}
	
	const char *query ="SELECT c.relname, n.nspname, CASE c.relkind WHEN 
'r' THEN 'BASE TABLE' WHEN 'v' THEN 'VIEW' WHEN 'm' THEN 'MATERIALIZED 
VIEW' END FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n 
ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v', 'm') AND 
n.nspname <> 'information_schema' AND n.nspname !~ 'pg_toast' AND 
c.relname = '&1' AND n.nspname = '&2';";
						
	PGresult *res;
	char *schema;

	get_table_schema(&table, &schema);
	//fprintf(stderr, "get_table_schema: %s %s\n", schema, table);
		
	if (do_query(db, "Unable to check table: &1", &res, query, 2, table, 
schema))
		return NULL;

	if (PQntuples(res) > 1)
	{
		GB.Error("Non-existant or ambiguous table name - &1", table);
		return NULL;
	}
	
	char *type = GB.NewZeroString(PQgetvalue(res, 0, 2));
	//fprintf(stderr, "get_table_type: %s\n", type);
	
	PQclear(res);
	;
	return type;
	
}
---------------------------------

Hope you like it

cheers
bruce
-------------- next part --------------
/*****************************************************************************

	table_type()

	Returns or sets the table type.

	<db> is the database handle.
	<table> is the table name.
	<newtype> is the new type

	This function returns the table type.

	NOTE: Postgresql does not allow setting the table type under normal circumstances.

*****************************************************************************/

static char *table_type(DB_DATABASE *db, const char *table, const char *newtype)
{
	if (newtype)
	{
		GB.Error("PostgreSQL does not allow setting the table types");

		return NULL;
	}
	
	const char *query ="SELECT c.relname, n.nspname, CASE c.relkind WHEN 'r' THEN 'BASE TABLE' WHEN 'v' THEN 'VIEW' WHEN 'm' THEN 'MATERIALIZED VIEW' END FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v', 'm') AND n.nspname <> 'information_schema' AND n.nspname !~ 'pg_toast' AND c.relname = '&1' AND n.nspname = '&2';";
						
	PGresult *res;
	char *schema;

	get_table_schema(&table, &schema);
	//fprintf(stderr, "get_table_schema: %s %s\n", schema, table);
		
	if (do_query(db, "Unable to check table: &1", &res, query, 2, table, schema))
		return NULL;

	if (PQntuples(res) > 1)
	{
		GB.Error("Non-existant or ambiguous table name - &1", table);
		return NULL; 
	}
	
	char *type = GB.NewZeroString(PQgetvalue(res, 0, 2));
	//fprintf(stderr, "get_table_type: %s\n", type);
	
	PQclear(res);
	;
	return type;
	
}