Re: 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(at)gmail(dot)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(at)gmail(dot)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(at)postgresql(dot)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...

On Thu, May 3, 2012 at 12:41 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> 2012/5/3 Chrishelring <christianhelring(at)gmail(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
AGENCY Software
A data system that puts you in control
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(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(at)gmail(dot)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(dot)f(dot)lundin(at)gmail(dot)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