Hi @SB699925 , based on other responses to this type of thing in the past, it looks like you are on the right track. You might try dropping the double quotes around stage_area. Also, I believe Postgres is case sensitive. So, if the schema or stored proc is not all lower case the way you are calling it, there is a high chance it will fail due to case. Let us know if this helped at all!
The script I included runs successfully on postgres so I think that's not the issue. I could try removing the double-quotes.
I believe the issue is that Matillion is expecting a result and wants to update/create a table based on the resultset, is my understanding correct? I think that's why the original post on this topic include the additional "select" statement.
The error is this: No results found from query. A table will not be created.
I'm just trying to understand how to resolve that at the moment. :D
Hi @SB699925, you are correct in that it will need to write back to a table which is why you need the SELECT 1 as test statement. The other thing I noticed in other people's comments is that you can't use the Sample button. You just have to run it.
It is possible that the functionality has changed as the posts around this subject are older and the product has changed quite a bit over time.
In your case, I might recommend a Python module called "psycopg2" as it's specifically built for Postgres and makes the configuration a bit more straightforward.
I don't have a Postgres instance I can connect to right now or I would test it myself. Hopefully this helps
Cheers for the responses, I really appreciate that.
I have implemented the psycopg2 module and tested that in a python script. It seems to "work" with one caveat.
I am using the python script to call a stored procedure in postgres. The stored procedure inserts a single record into a logging table. The stored procedure works fine when executed manually outside of Matillion. Here's where things get "strange": when I run the stored procedure through Matillion (via python script) it appears to run without error, however a record is *not* inserted - BUT the primary key is advanced.
I discovered this after running the stored procedure manually again, it had inserted records with a primary key (ID) that skipped the number of times I had run the procedure in Matillion. Very odd.
And this is a behaviour I actually noticed in the standard Database Query module but since it was also giving me the error I reported in my initial post I didn't pay much attention.
Have you ever experienced something like this before? I can post the stored proc here with screenshots or a sample table structure if that helps.
I thought perhaps this has something to do with permissions on the database or the security context of Matillion, I'm totally guessing though.
hehe - I just discovered that in python I need to execute a 'commit' statement after executing the stored procedure. Once I did that all is good - the stored procedure is storing the inserted record in the remote postgres table. 😅