Hi,

I am trying to tidy up my perl script that runs as a cgi and allows
remote users (aka me) to interact with my Pg database.

I primarily want to use this tool as a way for my iPad to
browse my data base. It seems to be working quite well but
one certainly does not want to 'select * from ginormous_table'.

I am trying to build a 'describe' function. ie if the user
types "describe tablename" in the sql box I want to display
some interesting attributes for that table, mainly names and
data types for each of the columns.

I have done this in other programs but I forced the user
to specify a schema qualified name ( and mimiced what psql -E
did).

So I guess the question is:
Given a bare table name, how can I recover the schema
qualified name with whatever the current search path happens
to be?

This task has to be done using simple sql from the perl dbi.

Thanks,

Jerry

Search Discussions

  • DM at Jan 19, 2011 at 8:12 pm
    one of the way to find out schema name is like below

    select * from pg_tables where tablename like '%xyz%';
    schemaname | tablename | tableowner | tablespace | hasindexes | hasrules |
    hastriggers
    ------------+-----------+------------+------------+------------+----------+-------------
    (0 rows)

    ~Deepak

    On Wed, Jan 19, 2011 at 8:26 AM, Jerry LeVan wrote:

    Hi,

    I am trying to tidy up my perl script that runs as a cgi and allows
    remote users (aka me) to interact with my Pg database.

    I primarily want to use this tool as a way for my iPad to
    browse my data base. It seems to be working quite well but
    one certainly does not want to 'select * from ginormous_table'.

    I am trying to build a 'describe' function. ie if the user
    types "describe tablename" in the sql box I want to display
    some interesting attributes for that table, mainly names and
    data types for each of the columns.

    I have done this in other programs but I forced the user
    to specify a schema qualified name ( and mimiced what psql -E
    did).

    So I guess the question is:
    Given a bare table name, how can I recover the schema
    qualified name with whatever the current search path happens
    to be?

    This task has to be done using simple sql from the perl dbi.

    Thanks,

    Jerry



    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general
  • Jerry LeVan at Jan 19, 2011 at 9:46 pm

    On Jan 19, 2011, at 3:12 PM, DM wrote:

    one of the way to find out schema name is like below

    select * from pg_tables where tablename like '%xyz%';
    schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
    ------------+-----------+------------+------------+------------+----------+-------------
    (0 rows)

    ~Deepak
    That does not work if the user entered a 'view' in the sql box.

    Also a table/view could be in several schemas...

    Jerry
  • DM at Jan 19, 2011 at 9:54 pm
    If your looking for Views then you could use pg_views ==> select * from
    pg_views limit 1;

    Here is one more, there was a recent post same as your request, Please see
    the below email, hope this helps you

    Tom Lane =================>
    Thomas Kellerer <spam_eater@gmx.net> writes:
    Jerry LeVan, 19.01.2011 17:35:
    So I guess the question is:
    Given a bare table name, how can I recover the schema
    qualified name with whatever the current search path happens
    to be?
    SELECT table_schema
    FROM information_schema.tables
    WHERE table_name = 'your_table'
    ;
    That's not going to work, at least not in the interesting case where you
    have more than one candidate table --- that SELECT will list all of 'em.

    In most cases the answer to this type of problem is "use regclass",
    but regclass doesn't quite solve Jerry's problem because it won't
    schema-qualify the name if the table is visible in the search path.
    The best solution I can think of is

    select nspname from pg_namespace n join pg_class c on n.oid = c.relnamespace
    where c.oid = 'my_table_name'::regclass;

    which works but seems a bit brute-force.

    regards, tom lane
    - Hide quoted text -

    ==========================================>
    On Wed, Jan 19, 2011 at 12:58 PM, Jerry LeVan wrote:

    On Jan 19, 2011, at 3:12 PM, DM wrote:

    one of the way to find out schema name is like below

    select * from pg_tables where tablename like '%xyz%';
    schemaname | tablename | tableowner | tablespace | hasindexes | hasrules
    hastriggers
    ------------+-----------+------------+------------+------------+----------+-------------
    (0 rows)

    ~Deepak
    That does not work if the user entered a 'view' in the sql box.

    Also a table/view could be in several schemas...

    Jerry
  • DM at Jan 19, 2011 at 9:59 pm
    or you could use the below query


    ********* QUERY **********
    SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
    'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
    pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','S','')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
    AND c.relname like '%my_table_name%'
    ORDER BY 1,2;
    **************************


    On Wed, Jan 19, 2011 at 1:54 PM, DM wrote:

    If your looking for Views then you could use pg_views ==> select * from
    pg_views limit 1;

    Here is one more, there was a recent post same as your request, Please see
    the below email, hope this helps you

    Tom Lane =================>
    Thomas Kellerer <spam_eater@gmx.net> writes:
    Jerry LeVan, 19.01.2011 17:35:
    So I guess the question is:
    Given a bare table name, how can I recover the schema
    qualified name with whatever the current search path happens
    to be?
    SELECT table_schema
    FROM information_schema.tables
    WHERE table_name = 'your_table'
    ;
    That's not going to work, at least not in the interesting case where you
    have more than one candidate table --- that SELECT will list all of 'em.

    In most cases the answer to this type of problem is "use regclass",
    but regclass doesn't quite solve Jerry's problem because it won't
    schema-qualify the name if the table is visible in the search path.
    The best solution I can think of is

    select nspname from pg_namespace n join pg_class c on n.oid =
    c.relnamespace
    where c.oid = 'my_table_name'::regclass;

    which works but seems a bit brute-force.

    regards, tom lane
    - Hide quoted text -

    ==========================================>

    On Wed, Jan 19, 2011 at 12:58 PM, Jerry LeVan wrote:

    On Jan 19, 2011, at 3:12 PM, DM wrote:

    one of the way to find out schema name is like below

    select * from pg_tables where tablename like '%xyz%';
    schemaname | tablename | tableowner | tablespace | hasindexes |
    hasrules | hastriggers

    ------------+-----------+------------+------------+------------+----------+-------------
    (0 rows)

    ~Deepak
    That does not work if the user entered a 'view' in the sql box.

    Also a table/view could be in several schemas...

    Jerry

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 19, '11 at 4:26p
activeJan 19, '11 at 9:59p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

DM: 3 posts Jerry LeVan: 2 posts

People

Translate

site design / logo © 2022 Grokbase