how to read tables from database

[anuragpj]

I have to get all the table names that exist in a particular database in pgsql and print it in a html page.
How can i do this?

[michaelb]

One way to get all user tables in Postgresql is to run this query:

SELECT c.relname as "Name"
  FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1

If you have one of the latest versions of the server you can make use of
Information Schema

This would be a preferred approach.

All this implies that you need to submit your query using some client API, such as PHP, but if you open the psql session the command is simply
\d

Hope it helps.

[michaelb]

Forgot to mention: as far as making connection and processing query results using PHP you need to look here

[anuragpj]

One way to get all user tables in Postgresql is to run this query:

SELECT c.relname as "Name"
  FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1

If you have one of the latest versions of the server you can make use of
Information Schema

This would be a preferred approach.

All this implies that you need to submit your query using some client API, such as PHP, but if you open the psql session the command is simply
\d

Hope it helps.

It is not working. It show this error
Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "'" in /web/train06/myphp/ex.php on line 14. What is the problem?
How to display the table names?

[michaelb]

This sql works perfectly well for me.
I suspect that you may need to escape the single quotes in your PHP code.
Since you did not post any code snippets it is hard to pinpoint the problem.

[ronverdonk]

It is not working. It show this error
Warning: PostgreSQL query failed: ERROR: parser: parse error at or near "'" in /web/train06/myphp/ex.php on line 14. What is the problem?
How to display the table names?

Since you ask the question in the PHP forum, we can show you how to code Pgsql commands in php.

What you should know, as the pgsql programmer/user, is how the pgsql command to show the tables is constructed. Like in MySQL it is SHOW TABLES". There must be something alike in pgsql.

Ronald :cool: