[Gambas-user] Postgres : Connection.Tables

[Benoît Minisini]

Le 31/10/2020 à 21:56, Bruce a écrit :
> 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
> 
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
> 


The "table type" has actually nothing to do with whether the table is a 
view or not, but is related to the storing engine used. It's a MySQL 
thing, and afaik PostgreSQL stores all tables the same way.

The concept of table view must be introduced in another way that does 
not exist at the moment.

Regards,

-- 
Benoît Minisini