I'm trying to figure out if PowerShell can pipe input from right to left similar to the Bash '<<<' operator. I encountered a scenario where I want to insert sanitized data into a PostgreSQL database using the command line tool `psql`. In Bash, I can use a command like this to pipe directly into `psql`: "INSERT INTO table (a, b, c) VALUES ('a', 'b', 'c');" | psql $databaseSSH.
To incorporate sanitized inputs, `psql` supports defining session variables with the '-v' flag: psql $databaseSSH -v a="A" b="B" c="C" <<< "INSERT INTO table (a, b, c) VALUES (:'a', :'b', :'c');". Unlike PowerShell, Bash's '<<<' sends input from right to left, which is crucial since the variables for the session must be set before `psql` can read them. When I try to use a regular pipeline input like this: "INSERT INTO table (a, b, c) VALUES (:'a', :'b', :'c');" | psql $databaseSSH -v a="A" b="B" c="C", I get a syntax error because the variables aren't defined beforehand. Are there any workarounds for this, other than using ODBC or defining parameters within the insert statement with PostgreSQL's set?
3 Answers
Actually, PowerShell's input behavior is a bit different from Bash. In PowerShell, the whole command runs in the same scope, so you don’t need to use the '<' operator as you would in Bash. The pipeline input will only be processed when `psql` reads from standard input, so make sure to run that pipeline command correctly. You also need to define variables using the '-v' flag individually for each one you want to pass on.
Have you considered just using the '<<<' operator directly in PowerShell? It might be handled by `psql` instead of PowerShell itself. It’s worth checking out!
I looked into that, but as far as I know, '<<<' is specific to Bash and isn't recognized in PowerShell or psql.
One option could be to use the '-c' argument to provide your SQL command directly rather than via standard input. For instance, you can run: psql $databaseSSH -v a="A" b="B" c="C" -c "INSERT INTO table (a, b, c) VALUES (:'a', :'b', :'c');". Alternatively, you might want to write your SQL commands to a file and then use that with `psql`, which keeps things tidier and avoids stdin issues. You can do this with psql -f -v ...; it’s a clean workaround!
That's a good idea! But I found that using the '-v' flag with the '-c' flag doesn't work, which I can confirm based on my testing. Writing the commands to a file might be the best route.
I did run the command in PowerShell, and it still throws a syntax error because psql tries to read from stdin before the variables are initialized.