Im currently working on my shiny app, and what im dealing with now is viewing return message from SQL function on shiny app, after using this function.
Here is my function:
CREATE OR REPLACE FUNCTION delivery(s_i INTEGER, n INTEGER, q INTEGER, pr DECIMAL(10,2)) RETURNS TEXT AS $$
BEGIN
INSERT INTO inbound(inb_supplier_id, name, quantity, price) VALUES(s_i, n, q, pr);
RETURN 'This delivery was succesfully added into data base';
END;
$$ LANGUAGE 'plpgsql';
And part of my shiny app:
tabPanel("Inbound",
sidebarLayout(
sidebarPanel(
radioButtons('INBVIEW', h3('Choose the data set'),
choices = list('This month deliveries' = "month",
'This day deliveries' = "day")),
tags$br(),
tags$br(),
h3(helpText('Add new delivery')),
textInput('INBsup', label = 'supplier id', value = 'id'),
textInput('INBpro', label = 'product id', value = 'id'),
textInput('INBq', label = 'quantity', value = 'quantity'),
textInput('INBp', label = 'price', value = 'price'),
actionButton(inputId = "INBZATWIERDZ", label = "Add", icon = icon("refresh"))
),
mainPanel(
DT::dataTableOutput(outputId = 'INBtab1'),
textOutput(outputId = "INBtab2")
)
)),
server <- function(input, output){
output$INBtab1 <- DT::renderDataTable({
INBpom <- switch(input$INBVIEW,
month = 1,
day = 2)
if (INBpom == 1){
dbGetQuery(con, "SELECT * FROM inbound WHERE EXTRACT(MONTH FROM delivery_date) = EXTRACT(MONTH FROM CURRENT_DATE);")
}
else{
dbGetQuery(con, "SELECT * FROM inbound WHERE delivery_date = CURRENT_DATE;")
}
})
observeEvent(input$INBZATWIERDZ,{
sup <- input$INBsup
pro <- input$INBpro
quan <- input$INBq
price <- input$INBp
output$INBtab2 <- DT::renderDataTable({
fetch(dbSendQuery(con, paste0("SELECT delivery(",sup,",",pro,",",quan,",",price,");")))
})
})
}
This function works of course, but unfortunately i don't know how to display this 'This delivery was succesfully added into data base' in my shiny app.
Thanks in advance!
dbSendQueryreturn an object with that message? Otherwise you might try usingsink()to a tempfile.dbSendQueryreturns a text message, try displaying it withrenderTextin atextOutputfield (not as a DT::datatable, which expects a dataframe).