On Thu, Sep 01, 2005 at 06:13:13PM +0200, Dick Kniep wrote:
>
> cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', true)
> cvix-# FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2
> cvix-# WHERE uid = 'zon0023'
> cvix-# AND t1.administratie_id = t2.administratie_id;
> set_config
> ------------------
> "testschema"
> (1 row)
Using quote_ident(t2.schema) would be cleaner and more reliable
than quoting t2.schema yourself, and the final "true" argument to
set_config() means that the setting is local to the current
transaction.
> Now we have installed a new server, with the same db version, the same content
> (a restore from the original db) and the same coding.
>
> After setting the search path the query
>
> Select * from vwexternetoegang
>
> produces the required results in the first installation, but in the new
> installation, it cannot find the view.
Have you used "SHOW search_path" to see what the setting is after
executing the above command? Is the new installation using autocommit
mode? If so then the setting won't take effect because you've told
set_config() that the setting is local to the current transaction,
and as soon as the SELECT completes that transaction is done. If
you want the setting to persist then pass "false" as the last argument
to set_config().
> I can reproduce the result not only from my own code, but also from psql.
Are you saying that the query works as expected with psql on the
old server, but not on the new server? If you run "\set" in psql,
what are the two servers' AUTOCOMMIT settings? If this is an
autocommit issue then it must be on the client side, because 7.4
removed support for server-side autocommit and your Subject header
says you're running 7.4.8.
--
Michael Fuhr