CODEWITHSUNDEEP
postgresqlhaskellpersistentyesod
Clark Gaebel
Clark Gaebel

Reputation: 17948

Yesod persistent-postgresql rawSql queries with column-list wildcards producing syntax errors

I'm using the persistent-postgresql library from Yesod, and I'd like to execute the following raw query:

SELECT * FROM utterance WHERE is_target IS NULL ORDER BY RANDOM() LIMIT 1000

to select 1000 random utterances with a NULL is_target. However, persistent generates the following SQL when I run my code through rawSql:

SELECT * FROM utterance WHERE is_target IS NULL ORDER BY RANDOM() LIMIT 1000"utterance"."id", "utterance"."message", "utterance"."is_target"

This generates an error in postgresql of syntax error at or near ""utterance"" at character 77.

What am I doing wrong?

Upvotes: 3

Views: 912

Answers (2)

Clark Gaebel
Clark Gaebel

Reputation: 17948

I fixed this by using the following query instead:

SELECT ?? FROM utterance WHERE is_target IS NULL ORDER BY RANDOM() LIMIT 1000

Upvotes: 4

SilverbackNet
SilverbackNet

Reputation: 2074

rawSql doesn't work with column wildcards, because it enforces strong typing on returned data, so it's trying (and failing) to figure out where to put the column names. You need to explicitly list column names OR use some number of "??" placeholders in the statement and bind them at runtime like,

$ (Entity myType utterance, .... ) -> do ....

If you don't want strong typing, you probably also don't want to use Persistent; that's the entire reason it exists.

Upvotes: 2

Related Questions