[R-sig-DB] RPostgreSQL and views

[Sebastian P. Luque]

On Mon, 23 Feb 2009 12:12:53 -0500,
Sean Davis <sdavis2 using mail.nih.gov> wrote:

> On Mon, Feb 23, 2009 at 11:41 AM, Sebastian P. Luque <spluque using gmail.com>wrote:
>> Hi,

>> Is there some way to access PosgreSQL Views with RPostgreSQL?
>> dbListTables only lists the tables, and I cannot find a similar
>> function in the DBI nor RPostgreSQL packages that would do this nor
>> access the view directly.  Any advice welcome.  Thanks.


> RPostgreSQL is simply an interface to work with Postgresql.  There are
> a few convenience features, but you can, of course, build more of your
> own.  The SQL for listing views is something like:

> 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", r.rolname as
> "Owner" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_roles r ON r.oid
> = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
> c.relnamespace WHERE c.relkind IN ('v','') AND n.nspname <>
> 'pg_catalog' AND n.nspname !~ '^pg_toast' AND
> pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2;

Thanks Sean, this sounds similar to what Adam was suggesting.  It would
be nice to have a method dbListViews() in RPostgreSQL, if the developers
agree. For now, I'll just wrap this into a local dbListViews() function.


> You could wrap that into a function, if you like, and have something
> like dbListViews.

> As for working with views in Postgresql, they work just like tables.
> You can use dbReadTable() with a view as well as a table.  Of course,
> you cannot insert into views without further database logic in place,
> but reading is identical to tables.

> If you have not used postgresql much before, a good place to start is
> by using psql with the -E option:

> psql -E

> Then, you can see the SQL generated when you look at tables, views,
> indexes, etc.

Thanks for the tips, I've been using PostgreSQL for a couple of years
now, but my SQL gets rusty soon after using it, especially for anything
beyond the simplest queries!


-- 
Seb