[R-sig-DB] RPostgreSQL and views

[Sean Davis]

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;

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.

Sean

	[[alternative HTML version deleted]]