PostgresSQL working with Schemas

Databases
1

Hello,

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?

Thanks

Björn

Sorry for my bad english :wink:

2

What do you mean?
We use the search_path and have no problems. What exactly isn’t working?

3

Hello Dirk,

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!?

Björn

4

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.

5

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