Thread: Determining current database programmatically
Hello, I am writing a stored procedure that should ideally alter its behavior depending on the database that it is in. (If it's being used in the test database it should do one thing, and in production it should do another - it uses pgMail to send email alerts and I don't want to be sending the support folks emails from my own testing). I want to keep the procedure generic, so is there a way to figure out the name of the current database programmatically? Like the Postgres equivalent of an environment variable or something? I've been poking around the docs but I don't even know what it is that I am looking for. :-) One alternative I can think of is to have a column in the databases that list the email address that the message should go to, but this means that I would no longer be able to use the same data loading scripts on both databases like I can do now. I can do it this way if necessary but I was looking for something a little more dynamic. Thanks, Fran
Fran Fabrizio <ffabrizio@mmrd.com> writes: > I am writing a stored procedure that should ideally alter its behavior > depending on the database that it is in. (If it's being used in the test > database it should do one thing, and in production it should do another > - it uses pgMail to send email alerts and I don't want to be sending > the support folks emails from my own testing). I want to keep the > procedure generic, so is there a way to figure out the name of the > current database programmatically? I don't think there is anything available at the SQL or plpgsql level that tells that. While it wouldn't be hard to add, ISTM that wiring a dependency on database name into your procedures is going to be a mistake in the long run. Why don't you set up a "configuration" table in each database that tells the procedure what to do? regards, tom lane
Fran Fabrizio <ffabrizio@mmrd.com> writes: > One alternative I can think of is to have a column in the databases > that list the email address that the message should go to, but this > means that I would no longer be able to use the same data loading > scripts on both databases like I can do now. I can do it this way > if necessary but I was looking for something a little more dynamic. Really, I'd go ahead and do the above. It means you can change/add email addresses easily without bringing the system down. As for loading, you could add an argument to the load script controlling which set of addresses gets added, eg: $ ./loaddata dev $ ./loaddata prod -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863