R help - Oct 2009 - Re trieving comments from PostgreSQL tables with RODBC and psqlodbc

I don't know whether this is an RODBC issue, a psqlodbc issue or a
PostgreSQL
issue; I've searched the documentation and forums for each, with no success.

I want to list all the tables in a PostgreSQL schema, and return the table
comments as well as the table names.  As an example, one table is defined as
follows:

CREATE TABLE scen990.ezone_locationprice_year1
(
  ezone integer NOT NULL,
  lprice_man_year1 real,
  lprice_war_year1 real,
  lprice_ret_year1 real,
  lprice_gen_year1 real,
  lprice_med_year1 real,
  lprice_gov_year1 real,
  CONSTRAINT ezone_locationprice_year1_pkey PRIMARY KEY (ezone)
) 
WITHOUT OIDS;
ALTER TABLE scen990.ezone_locationprice_year1 OWNER TO mscope;
COMMENT ON TABLE scen990.ezone_locationprice_year1 IS 'calculated';

The RODBC function sqlTables() returns all the tables, but the records look
like this:

TABLE_QUALIFIER     TABLE_OWNER     TABLE_NAME     TABLE_TYPE     REMARKS
mscope     scen990     ezone_locationprice_year1     TABLE     ""

All the "REMARKS" values are empty strings.  Can I get it to return
the
COMMENT ON values instead?  (the table_owner and tablet_qualifier are
switched but that doesn't worry me)   I'm using  R 2.8.1, RODBC 1.2-4,
psqlodbc 8.0.2, and PostgreSQL 8.1.5 .  

Regards,
Jim Cser
-- 
View this message in context:
http://www.nabble.com/Retrieving-comments-from-PostgreSQL-tables-with-RODBC-and-psqlodbc-tp26104999p26104999.html
Sent from the R help mailing list archive at Nabble.com.

You seriously need to update your way-obsolete software (see the 
posting guide), but you are seeing what your ODBC driver is returning, 
not an R issue.

R-sig-db is a better list for help on database connectivity.

On Wed, 28 Oct 2009, Jim Cser wrote:
>
> I don't know whether this is an RODBC issue, a psqlodbc issue or a
PostgreSQL
> issue; I've searched the documentation and forums for each, with no
success.
>
> I want to list all the tables in a PostgreSQL schema, and return the table
> comments as well as the table names.  As an example, one table is defined
as
> follows:
>
> CREATE TABLE scen990.ezone_locationprice_year1
> (
>  ezone integer NOT NULL,
>  lprice_man_year1 real,
>  lprice_war_year1 real,
>  lprice_ret_year1 real,
>  lprice_gen_year1 real,
>  lprice_med_year1 real,
>  lprice_gov_year1 real,
>  CONSTRAINT ezone_locationprice_year1_pkey PRIMARY KEY (ezone)
> )
> WITHOUT OIDS;
> ALTER TABLE scen990.ezone_locationprice_year1 OWNER TO mscope;
> COMMENT ON TABLE scen990.ezone_locationprice_year1 IS 'calculated';
>
> The RODBC function sqlTables() returns all the tables, but the records look
> like this:
>
> TABLE_QUALIFIER     TABLE_OWNER     TABLE_NAME     TABLE_TYPE     REMARKS
> mscope     scen990     ezone_locationprice_year1     TABLE     ""
>
> All the "REMARKS" values are empty strings.  Can I get it to
return the
> COMMENT ON values instead?  (the table_owner and tablet_qualifier are
> switched but that doesn't worry me)   I'm using  R 2.8.1, RODBC
1.2-4,
> psqlodbc 8.0.2, and PostgreSQL 8.1.5 .
>
> Regards,
> Jim Cser
> --
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595