Skip to content

RPostgreSQL and views

5 messages · Sébastien Bihorel, adam_pgsql, Sean Davis

Original
Sébastien Bihorel
# 
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.


Cheers,
adam_pgsql
#
On 23 Feb 2009, at 16:41, Sebastian P. Luque wrote:

            

      
      
      
    

        
I have not used RPostgreSQL before, and this is untested, but i guess  
you could add this function to RPostgreSQL/R/PostgreSQL.R

setMethod("dbListViews", "PostgreSQLConnection",
           def = function(conn, ...){
               out <- dbGetQuery(conn,
                                 "select viewname from pg_views where  
schemaname !='information_schema' and schemaname !='pg_catalog'",
                                 ...)
               if (is.null(out) || nrow(out) == 0)
                   out <- character(0)
               else
                   out <- out[, 1]
               out
           },
           valueClass = "character"
           )

HTH

adam

  
  


  


      

    

    

      
      

        


  

        

      Sean Davis
    

    

      
      #
    

  


  

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

        

            

      
      
      
    

        
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

  
  


  


      

    

    

      
      

        


  

        

      Sébastien Bihorel
    

    

      
      #
    

  


  

      
On Mon, 23 Feb 2009 12:12:53 -0500,

        
Sean Davis <sdavis2 at mail.nih.gov> wrote:

        

            

      
      
      
    

        
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.

            

      
      
      
    

        
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!

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Sean Davis
    

    

      
      #
    

  


  

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

        

            

      
      
      
    

        
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