as a newbie in PostgresSQL and XOJO i have an question about Schemas in Postgres using XOJO.
i’ve added some new Schemas to my PG Database and moved some Tables (from public Schema) into. Now my Code for my Recordsets (SQLExecute) doesn’t work any more. So i started reading the Postgres Docs and found out that you can set the search_path variable. As i understand this variable is not Server based?!? It seems to be client session based.
So i added the following line to my code after connecting to the Database:
app.db.SQLExecute(“SET search_path TO Schema1,Schema2,Schema3,Schema4,Schema5;”)
But this will not solve the problem. I don’t want explicit write the “SCHEMA”.“TABLE/VIEW” syntax in my SQL Statements in XOJO.
Is there a solution for that?
Sorry for my bad english
What do you mean?
We use the search_path and have no problems. What exactly isn’t working?
thanks for your Reply
the Problem is solved
I named the first character of the Schema Name in UpperCase (Schema1)
then the search_path didn’t work
then i renamed it to schema1 and it works immediately
i think its really better to name every object in the database in lower cases!?
so my solution is writing the following line of code to set search_path:
app.db.SQLExecute(“SET search_path TO schema1,schema2,schema3,schema4,schema5;”)
Then the db objects are found in the expected order.
Well, if those objects contain capital letters, you need to quote them like this:
Select “LASTNAME” from people
If you instead go
Select LASTNAME from people , the Postgres parser will read that as
Select lastname from people