[R-sig-DB] RPostgreSQL and views

[Sean Davis]

On Mon, Feb 23, 2009 at 1:22 PM, Sebastian P. Luque <spluque using gmail.com>wrote:

> 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.
>

It is possible to add these DB-specific things to the package and this has
probably been done with other drivers.  However, the DBI interface does not
specify that there should be a dbListViews() (to my knowledge, anyway),
which might be why it is not implemented, but I am only speculating.

Sean



>
>
> > 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
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]