This topic was automatically generated from Slack. You can find the original thread here.
Quick question, to confirm in a PostgreSQL step that has an execute custom SQL query - does a “$” symbol try and call to a variable in the Pipedream workflow and needs to be escaped to properly execute?
You’ll probably need something like,
`select * from users where id = '${steps.foo.$return_value}'
ok gotcha, it’s more of I’m sending a query that has a $ as in:
Their commitment to collaboration is underscored by securing $20 million in Series C funding and forming significant partnerships, such as with Unum Group, to advance caregiver support initiatives.
so it sounds like I need to escape the $ within the sentence, if that’s correct?
That string is part of your query? Does surround the entire string in quotes not work?
it does not unfortunately
By “does not work” do you mean there’s an error thrown or your query isn’t returning the results you’re expecting?
Apologies, that was vague and not very helpful - it messes up the import by throwing off the values:
Error
The number of values provided does not match the number of values in the query.
If there are no $ signs, everything is fine, but the section one gets added into the input it throws this error. I am escaping all the normal SQL characters
Got it, thanks for the details, so you’re inserting data in this particular query.
Couple of follow up questions:
Here’s an example of the portion:
WITH input_data AS ( SELECT ‘cariloop.com’ AS company_url, ‘Partner Program’ AS company_url_category, ‘Cariloop AS company_offering_url_source, CASE WHEN jsonb_typeof(’[ { “company_offering”: “Cariloop emphasizes the importance of strategic partnerships to amplify its reach and impact, providing innovative caregiver support solutions tailored to enhance HR initiatives, increase workforce engagement, and attract talent while bolstering diversity, equity, and inclusion programs. It engages with a wide array of partners across different sectors including benefits technology, benefit navigation, total well-being, insurance, and retail pharmacy. Cariloop’’s approach to partnerships includes offering resources for streamlined contracting, seamless implementation, integrated employee access platforms, and simplified administration and billing, aiming to create a supportive ecosystem for caregivers. Their commitment to collaboration is underscored by securing $20 million in Series C funding and forming significant partnerships, such as with Unum Group, to advance caregiver support initiatives.” } ]
i have it also if there isn’t an object response as well with a THEN and ELSE
Ah ok, so this is a pretty complex insert statement.
Is it possible that one of the other values for one of the other columns is missing for this particular case that fails?
It’s not, I’ve basically tested everything down to the $ and removing it works
it has to have the values to run in a step before when it escapes any of the text so it can’t get to this point without all values
Cool, that’s close then. Perhaps you can escape the $ in that same step:
value.replaceAll('$', "\$")
I’m not 100% sure if there’s a dedicated escape sequence for Postgres insertions, trying to find that out
Perfect, let me update that Node step right now and test it, and I’ll let you know if it works. I wasn’t sure if this was just something strange b/c I couldn’t find anywhere where you normally need to escape a “$”
Right, that’s strange to me too. It might be a signal for a variable or function in Postgres internally.
The other piece of advice I found is to wrap the whole statement in $$ signs to escape the contents:
This is a more authoritative source, which a few different escape strategies: