RPostgreSQL and views

On Mon, Feb 23, 2009 at 11:41 AM, Sebastian P. Luque <spluque at 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.