Database tables and fields list in a table not showing (v2022)

[sonc]

Hello,

My need is to list database tables and their fields in a table.
First step, I've created a sql that work fine.

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE 
	table_name IN 
		(SELECT tablename
		FROM pg_tables
		WHERE schemaname = 'xxx'
		AND tablename NOT IN ('xxx')
		AND tablename NOT ILIKE ('i18n_%')
		AND tablename NOT ILIKE ('stats_%'))
ORDER BY table_name ASC, column_name ASC;

Second step, I've created a new table to store the result with 2 more boolean colums

     CREATE TABLE xxx.data_model AS (MY_FIRST_SELECT_ABOVE)

Third step, I've added 2 boolean fileds

In PGADMIN or other sgbd tools, displaying data is fine.
After syncing with phpmaker 2022, only the 2 added columns are shown !

Any ideas ?
Help welcome.

Regards

[mobhar]

sonc wrote:

Third step, I've added 2 boolean fileds

How did you add it? From database side, or from PHPMaker project side using Custom Fields?

If you added those 2 fields from database side, then make sure you have already re-synchronized your project to database again.

[sonc]

Re,

added fields from database side.
A third one also added, a serial as primary key.

Result phpmaker resyncing with database.

#
#data_model
#
ADDED : (Field) data_model->column_name
ADDED : (Field) data_model->data_type
ADDED : (Field) data_model->fpat
ADDED : (Field) data_model->id
ADDED : (Field) data_model->pmi
ADDED : (Field) data_model->table_name

But in phpmaker, only added id, fpat and pmi are shown !

Thanks in advance

[sonc]

Re, also tryed to add linked table named 'columns' from 'information_schema' but not possible.

[sonc]

Ok, non-added fields are unsupported fields...

[sonc]

Then solution is to cast unsupported type, like

SELECT table_name::varchar(50), column_name::varchar(50), data_type::varchar(50)
...

Hope helps other.