UUIDs as variables passed to postgres dbquery / Removing quotes / ?

[arichman]

Hello,
I've tried a wide variety of queries but Postgres is returning errors when trying to pass a UUID in a variable to my query:
base search | table l_id, l_name | eval l_id_quotes="'".l_id."'" | map maxsearches=100 search="dbquery mydb \"select * from my_table where id LIKE '$l_id_quotes$' \""

The errors look like:
[map]: command="dbquery", A database error occurred: ERROR: syntax error at or near "027e435" Position: 47

Without the eval statement, I was unable to get a single-quoted value for postgres to not parse as a column. And if I exclude single quotes from around '$l_id_quotes$' then the query is always wrapped in double quotes, which causes the same problem with postgres.

In addition to WHERE id LIKE..., I've tried:
WHERE id::varchar='$l_id_quotes$'
WHERE CHARINDEX (id, '$_id_quotes$') > 0
WHERE id IN ('$_id_quotes$')
... all to return the same errors.

The query WHERE id::varchar='fake-literal-valid-uuid' actually works.

Anybody done this successfully with postgres?
Thanks!

[arichman · Accepted solution]

I'll answer my own question...
I resolved this issue with some egregious string manipulation...

base search | table l_id, l_name | eval sql_str="select name, id::varchar from my_table where id = '".$l_id$."';" | map maxsearches=100 search="| dbquery mydb $sql_str$"

[mishin]

thanks, very useful!!