Обсуждение: Uppercase to lowercase trigger?
Hi, I´ve got some tables with column names in lowercase. Before updatering these tables I want to add a trigger that can convert these lowercase to uppercase, and when the tables are updates convert them back to lowercase.. I´ve looked at several examples without any luck. Could someone put me in the direct direction? Thanks Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, May 2, 2012 at 10:33 PM, Chrishelring <christianhelring@gmail.com> wrote: > Hi, > > I´ve got some tables with column names in lowercase. Before updatering these > tables I want to add a trigger that can convert these lowercase to > uppercase, and when the tables are updates convert them back to lowercase.. Not entirely sure what you're looking for here, but in Postgres, if you don't quote your column names, they are lowercased by default. UPDATE some_table SET ColumnName = 'foo', COLUMNNAME2 = 'bar'; This will work if the table has "columnname" and "columnname2". ChrisA
Maybe I should clarify what I mean. I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about 200+ tables in it. Some of them is supposed to be updated using an application what requres that some of the columns are in uppercase (a leftover from Oracle I suppose) but the main application that uses these tables requres that the columns are in lowercase. I had an idea that using a trigger I could chance the columns from lowercase to uppercase before updating /changing the tables and after the update chance them back to lowercase. Is that possible and how do I do that? Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hello 2012/5/3 Chrishelring <christianhelring@gmail.com>: > Maybe I should clarify what I mean. > > I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about > 200+ tables in it. Some of them is supposed to be updated using an > application what requres that some of the columns are in uppercase (a > leftover from Oracle I suppose) but the main application that uses these > tables requres that the columns are in lowercase. I had an idea that using a > trigger I could chance the columns from lowercase to uppercase before > updating /changing the tables and after the update chance them back to > lowercase. Is that possible and how do I do that? no, this is not possible. But you can write simple functions that verifies names and you can call this function manually when you do some changes, or you can call this function from cron. Regards Pavel Stehule > > Christian > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
What if you created separate tables with the fields in uppercase, either with different names or better yet the same names and a different schema? It seems you could put triggers on those tables, and have your trigger translate the field names and make the updates to the real tables instead.
Seems like a lot of work, though, that could be avoided if you could get your app to drop the quoting of field names, and have it automatically fold to lower case...
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
On Thu, May 3, 2012 at 12:41 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2012/5/3 Chrishelring <christianhelring@gmail.com>:> Maybe I should clarify what I mean.no, this is not possible. But you can write simple functions that
>
> I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about
> 200+ tables in it. Some of them is supposed to be updated using an
> application what requres that some of the columns are in uppercase (a
> leftover from Oracle I suppose) but the main application that uses these
> tables requres that the columns are in lowercase. I had an idea that using a
> trigger I could chance the columns from lowercase to uppercase before
> updating /changing the tables and after the update chance them back to
> lowercase. Is that possible and how do I do that?
verifies names and you can call this function manually when you do
some changes, or you can call this function from cron.
Regards
Pavel Stehule
>
> Christian
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682473.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A data system that puts you in control
(253) 245-3801
On 05/03/12 12:50 AM, Ken Tanzer wrote: > What if you created separate tables with the fields in uppercase, > either with different names or better yet the same names and a > different schema? It seems you could put triggers on those tables, > and have your trigger translate the field names and make the updates > to the real tables instead. if one of the applications is read only on this table, you could use a view with the alternate version of the names. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Hmm.. Haden´t thought about making a view. It should work because the application that uses the data is read-only (yet!). I´ll give that a try. Thanks! Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384p5682880.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Chrishelring wrote: > Hmm.. Haden´t thought about making a view. It should work because the > application that uses the data is read-only (yet!). > > I´ll give that a try. Thanks! Well a view is a much more natural solution than the other proposal, about constantly doing data definition / changing the schemas for each access like this proposal. In fact, what you talk about sounds like an ideal case for views; you could have 2 complete schemas, one per application, where each corresponds to the other but one is lowercase and one is uppercase, and one is a view. -- Darren Duncan
On 2012-05-03, Chrishelring <christianhelring@gmail.com> wrote: > Maybe I should clarify what I mean. > > I have a db (postgresql 8.4.1 with a postGIS spatial extension) with about > 200+ tables in it. Some of them is supposed to be updated using an > application what requres that some of the columns are in uppercase (a > leftover from Oracle I suppose) but the main application that uses these > tables requres that the columns are in lowercase. I had an idea that using a > trigger I could chance the columns from lowercase to uppercase before > updating /changing the tables and after the update chance them back to > lowercase. Is that possible and how do I do that? No, you could try fashioning an updatable view in a different schema and exploiting schema search path such the main application sees one version and the legacy application sees the other. this means writing a view to handle selects, and do instead rules for updates and deletes. shema search path can be defaulted on a per-user basis so if one of the applications always uses a distincr username for the databse connection this can be implemented in a way that's transparent -- ⚂⚃ 100% natural
Hmm, I was under the impression that if you create the table with quoted field names, you get case-sensitive names, But if you create the tables without quotes around the the field names, pg will make them lowercase, But case-insensitive.... That way you don't need views or rewrite. Do create table foo( bar int); Instead of Create table "foo" ("bar" int); /björn lundin
On Tue, May 15, 2012 at 4:04 PM, björn lundin <b.f.lundin@gmail.com> wrote: > Hmm, I was under the impression that if you create the table with quoted field names, you get case-sensitive names, > But if you create the tables without quotes around the the field names, pg will make them lowercase, > But case-insensitive.... If they're quoted, case is retained; if they're not, they're lowercased. This rule applies to both creation and referencing, so if you always leave them unquoted, they are effectively case insensitive (technically they're case-folded), or if you always quote them, they're case sensitive. ChrisA