Thread: 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

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