Hi

To check if there is a table inside Postgre I can use TABLE() function, but to check if there is an index? I have not found any function to do this
How I can check if an index is definite in PGDBE?

all the best

On 23/06/21 12:37, Simone Degl'Innocenti wrote:
> Hi
> 
> To check if there is a table inside Postgre I can use TABLE() function, but to check if there is an index? I have not found any function to do this
> How I can check if an index is definite in PGDBE?
> 
> all the best
> 

SELECT
     indexname,
     indexdef
FROM
     pg_indexes
WHERE
     tablename = 'table_name';


https://www.postgresqltutorial.com/postgresql-indexes/postgresql-list-indexes/

HTH
Osvaldo

Hi Osvaldo

the select is correct, but PGDBE creates some additional index.
For example: my table with PGDBE have 2 indexes, but if I call this query in Postgres I have 7 indexes: 
- primary key index
- record index
- seek index
- 2 index for first index: 1 for like and 1 for seek
- 2 index for second index: 1 for like and 1 for seek


If I query the table 

SELECT * FROM public."alaska-software.isam.orders"

I can see the logical index that I see inside PGDBE.

I think that TABLE() function query this table

SELECT * FROM public."alaska-software.isam.tables"

but I have not found a function that queries alaska-software.isam.orders

This, in my opinion, maybe a lack of PGDBE core.

all the best


>
>SELECT
>     indexname,
>     indexdef
>FROM
>     pg_indexes
>WHERE
>     tablename = 'table_name';
>
>
>https://www.postgresqltutorial.com/postgresql-indexes/postgresql-list-indexes/
>
>HTH
>Osvaldo

On 24/06/21 3:33, Simone Degl'Innocenti wrote:
> Hi Osvaldo
> 
> the select is correct, but PGDBE creates some additional index.
> For example: my table with PGDBE have 2 indexes, but if I call this query in Postgres I have 7 indexes:
> - primary key index
> - record index
> - seek index
> - 2 index for first index: 1 for like and 1 for seek
> - 2 index for second index: 1 for like and 1 for seek
> 
> 
> If I query the table
> 
> SELECT * FROM public."alaska-software.isam.orders"
> 
> I can see the logical index that I see inside PGDBE.
> 
> I think that TABLE() function query this table
> 
> SELECT * FROM public."alaska-software.isam.tables"
> 
> but I have not found a function that queries alaska-software.isam.orders
> 
> This, in my opinion, maybe a lack of PGDBE core.
> 
> all the best
> 
> 
>>
>> SELECT
>>      indexname,
>>      indexdef
>> FROM
>>      pg_indexes
>> WHERE
>>      tablename = 'table_name';
>>
>>
>> https://www.postgresqltutorial.com/postgresql-indexes/postgresql-list-indexes/
>>
>> HTH
>> Osvaldo

Ok, I got it.

I dint know.

Best Regards
Osvaldo Ramirez

Usually you get primary keys, indexes and foreign keys from 
information_schema which is special system database.
Am not sure though if this is valid inside limited pgdbe though as I do 
not use it....

here is sample copy pasted from SzackOverflow

SELECT
     tc.table_schema,
     tc.constraint_name,
     tc.table_name,
     kcu.column_name,
     ccu.table_schema AS foreign_table_schema,
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name
FROM
     information_schema.table_constraints AS tc
     JOIN information_schema.key_column_usage AS kcu
       ON tc.constraint_name = kcu.constraint_name
       AND tc.table_schema = kcu.table_schema
     JOIN information_schema.constraint_column_usage AS ccu
       ON ccu.constraint_name = tc.constraint_name
       AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';



Simone Degl'Innocenti je 23.6.2021 ob 20:37 napisal:
> Hi
> 
> To check if there is a table inside Postgre I can use TABLE() function, but to check if there is an index? I have not found any function to do this
> How I can check if an index is definite in PGDBE?
> 
> all the best
>

Hi Matej

thanks for the code, but the correct approach in PGDBE I think is to use the alaska-software* tables.

I have tried to explain it better in the reply to Osvaldo tips.

all the best

Matej Jurac wrote in message news:42c65c10$e500a70$790b1@news.alaska-software.com...
>Usually you get primary keys, indexes and foreign keys from 
>information_schema which is special system database.
>Am not sure though if this is valid inside limited pgdbe though as I do 
>not use it....
>
>here is sample copy pasted from SzackOverflow
>
>SELECT
>     tc.table_schema,
>     tc.constraint_name,
>     tc.table_name,
>     kcu.column_name,
>     ccu.table_schema AS foreign_table_schema,
>     ccu.table_name AS foreign_table_name,
>     ccu.column_name AS foreign_column_name
>FROM
>     information_schema.table_constraints AS tc
>     JOIN information_schema.key_column_usage AS kcu
>       ON tc.constraint_name = kcu.constraint_name
>       AND tc.table_schema = kcu.table_schema
>     JOIN information_schema.constraint_column_usage AS ccu
>       ON ccu.constraint_name = tc.constraint_name
>       AND ccu.table_schema = tc.table_schema
>WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
>
>
>
>Simone Degl'Innocenti je 23.6.2021 ob 20:37 napisal:
>> Hi
>> 
>> To check if there is a table inside Postgre I can use TABLE() function, but to check if there is an index? I have not found any function to do this
>> How I can check if an index is definite in PGDBE?
>> 
>> all the best
>>

Simone,

>To check if there is a table inside Postgre I can use TABLE() function, 
>but to check if there is an index? I have not found any function to do this
>How I can check if an index is definite in PGDBE?

It looks like you would like a function that lists Index (Files) similar 
to Directory("*" + OrdBagExt()), assuming your Index Files contain only a 
single Index Tag, which isn't the case for CDX index files, for example.

You could make your own function, something like "TagList([<cTable>])", 
that queries the public.alaska-software.isam.orders table in PG, and returns 
a list of (all) Index (Tag) Names, as well as any other data that is 
available in that table, like the corresponding Table, the Key, etc.)

Or you want to simply see if an Index (Tag) (or File) exists. Then you would 
want something like "TagExists(<cTagName>, [<cOrdBagName>], [<cTableName>]) 
to determine if a particular Index (Tag) already exists in a File on Disk or 
in your Schema/Database/Server -- and if it belongs to a particular Table.

The question is: what do you want this Xbase++ function for or what would be 
the purpose for this function. If it makes sense and some others would like 
to have such a function, maybe Alaska will implement it (at some time)?

So, I would recommend you send a feature request (PDR) to Alaska. Or you 
could implement this yourself (and post your code here for others). As this 
deals with Alaska Xbase++ specific implementations (like the PGSQL table and 
field names), it would always be better if Alaska would implement this as 
part of the Xbase++ language, as they might change those internals at any 
time and without notification.

Hope that helps,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[L]:   https://www.LinkedIn.com/in/AndreasGehrsPahl
[F]:   https://www.FaceBook.com/AbsoluteSoftwareLLC