[R] Re trieving comments from PostgreSQL tables with RODBC and psqlodbc

[Prof Brian Ripley]

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