Passing a table name as a parameter in postgres-simple

Learn
#1

Hi! I’m trying to define the following function

clearTable :: String -> (Ctx, a) -> IO ()
clearTable tableName (ctx, _) = do
  _ <-
    Database.execute ctx.connection [sql| TRUNCATE TABLE ? |] [tableName]
  return ()

to have a reusable function to truncate tables for testing. However, I run into the error syntax error at or near \"'users'\. I suspect that postgres-simple adds '' to parameters, which makes sense for rows but tables should not be quoted. Is there any way to pass a non-quoted parameter?

#2

Try changing the type of tableName to Identifier or QualifiedIdentifier

1 Like
#3

Looks good, but Identifier doesn’t seem to have a ToRow instance. Which makes sense to me but that is the parameter required by execute. I changed it to


truncateTable :: Text -> (Ctx, a) -> IO ()
truncateTable text (ctx, _) = do
  _ <-
    Database.execute ctx.connection [sql| TRUNCATE TABLE ? |] (Identifier (text))
  return ()

And I got the error that No instance for ‘Database.ToRow Identifier’

#4

In order to pass a single parameter you can wrap it in Only. Only has ToRow/FromRow instances provided the wrapped type has ToField/FromField instances.

1 Like
#5

Table names can’t be query parameters. You should fall back to raw SQL (and associated risks!).
Apparently, they can, “neat” :sweat_smile:

Reconsider taking a shortcut in this particular place and make a bunch of per-table static queries.

2 Likes
#6

This works! Also just wrapping it in a list with a single element worked as well.

truncateTable :: Text -> (Ctx, a) -> IO ()
truncateTable text (ctx, _) = do
  _ <-
    Database.execute ctx.connection [sql| TRUNCATE TABLE ? |] [Identifier (text)]
  return ()