RPostgreSQL and views

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

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;

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