2

Our database is set up so that each of our clients is hosted in a separate schema (the organizational level above a table in Postgres/Redshift, not the database structure definition). We have a table in the public schema that has metadata about our clients. I want to use some of this metadata in a view I am creating.

Say I have 2 tables:

public.clients

  • name_of_schema_for_client
  • metadata_of_client

client_name.usage_info

  • whatever columns this isn't that important

I basically want to get the metadata for the client I'm running my query on and use it later:

SELECT * 
FROM client_name.usage_info
INNER JOIN public.clients 
ON CURRENT_SCHEMA() = public.clients.name_of_schema_for_client

This is not possible because CURRENT_SCHEMA() is a leader-node function. This function returns an error if it references a user-created table, an STL or STV system table, or an SVV or SVL system view. (see https://docs.aws.amazon.com/redshift/latest/dg/r_CURRENT_SCHEMA.html)

Is there another way to do this? Or am I just barking up the wrong tree?

R. Jutras
  • 331
  • 1
  • 4
  • 14

1 Answers1

0

Your best bet is probably to just manually set the search path within the transaction from whatever source you call this from. See this:

https://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html

let's say you only want to use the table matching your best client:

set search_path to your_best_clients_schema, whatever_other_schemas_you_need_for_this;

Then you can just do:

select * from clients;

Which will try to match to the first clients table available, which by coincidence you just set to your client's schema!

You can manually revert afterwards if need be or just reset the connection to return to default, up to you

dannymc129
  • 111
  • 5
  • So not really. For clarification, our database structure is that we have schemas for each client. I am building a query that will be stored as a view that runs on each client's schema to get data for a front end. When this query is run, the search path is already set but the code has no way to know what the search path is because it's in a view stored on that schema already. I am trying to get the name of the schema the view is being run on, but it could be any client's schema. Manually setting a search path doesn't really apply. – R. Jutras Apr 06 '18 at 20:07