tomoakin / RPostgreSQL

Automatically exported from code.google.com/p/rpostgresql
64 stars 20 forks source link

let dbSendQuery raise error if needed; return all cols for 0-row results #92

Open mmuurr opened 6 years ago

mmuurr commented 6 years ago

Two issues in this PR:

(1) Data frames returned by fetch should be 0 x n, not 0 x 0, when having zero rows.

Existing behavior:

!> dbGetQuery(conn, "SELECT col_1, col_2 FROM my_table LIMIT 0")
 data frame with 0 columns and 0 rows

New behavior:

!> dbGetQuery(conn, "SELECT col_1, col_2 FROM my_table LIMIT 0")
 [1] col_1 col_2
 <0 rows> (or 0-length row.names)

This allows for seamless rbind-ing when piecing together tables, and prevents column access errors on the returned data frame when the query results happen to have zero rows.

(2) postgresqlQuickSQL is returning NULL (with a warning) on error-ing statements. The calling code should be responsible for handling raised conditions (e.g. with a tryCatch block), otherwise scripts running non-interactively won't fail-fast.

Existing behavior (no error raised):

  > x <- dbGetQuery(conn, "SELECTT * FROM my_table")
 Error in postgresqlExecStatement(conn, statement, ...) :
   RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "SELECTT"
 LINE 1: SELECTT * FROM my_table
         ^
 )
 Warning message:
 In postgresqlQuickSQL(conn, statement, ...) :
   Could not create execute: SELECTT * FROM my_table
 > str(x)
  NULL

New behavior (error raised):

 > x <- dbGetQuery(conn, "SELECTT * FROM my_table")
 Error in postgresqlExecStatement(conn, statement, ...) :
   RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "SELECTT"
 LINE 1: SELECTT * FROM my_table
         ^
 )
!> str(x)
 Error in str(x) : object 'x' not found