Best way to approach this?

[mikew123]

Hello !

New to teiid and i've been working through the quickstart guides and created a UDF etc.

Now wondering how best to accomplish the following, please?

Basically, I need to modify all columns in a query result (lets say, to UPPER()).

This would seem to suit a UDF, but, I can't seem to pass the entire resultset into the UDF so I don't know how many columns there might be - and I want to avoid users having to change their behaviour.  For example, if a user runs "SELECT * FROM foo" I want all columns to be returned as uppercase. Equally, if the user named the columns explicitly "SELECT first, second, third FROM foo" i'd also run the UPPER() UDF against each one.. So functionally equivalent of doing "SELECT UPPER(first), UPPER(second), UPPER(third) FROM foo) without the user having to a) explicitly name all columns, nor add the function themselves, nor prevent them from doing a wildcard query..

Is this doable via a UDF? Or would a translator suit this better?

Thanks!!

Mike

[rareddy]

No UDF is not the answer. You need to create a View for the "Foo" table, that has transformation like

CREATE VIEW FooView (
  first varchar(25),
  second varchar(25),
  third varchar(30)
) AS
select UPPER(first) as first, UPPER(second) as second, UPPER(third) as third from foo;

then you can issue "select * from FooView" or " select first, second from FooView" and you will get desired results.

Ramesh..

[mikew123]

Ah! Thank you - I think I may have not been seeing the woods for the trees.  I still need a UDF for the function (its not UPPER but another text operation) but of course, creating a view for each table I need to map makes sense!! 

Thanks again,

Mike