>> Ian Hardingham <i...@omroth.com> wrote: >>> I'm just getting around to this. Can I do: >>> >>> ALTER TABLE userTable ADD upperName = upper(name) TEXT >>> >>> Will this retroactively and for all future inserts work? >> >> This is not a valid syntax, so it won't work - neither retroactively, in the present, nor in the future. It's not even clear to me what you are trying to achieve here. > >Some sort of stored function ? So he can store retrieve a pseudo-column from a TABLE and SQLite will perform a calculation for him. It might be possible to do it with a VIEW: > >CREATE VIEW funcViewUserTable AS SELECT *,upper(name) AS upperName FROM userTable
I'd guess he wants to add a new column to the table called "upperName" and populate it automatically with the uppercase version of the column "name", plus also have any new INSERTs also populate the column. If that's so, you'd need to do: ALTER TABLE userTable ADD upperName TEXT (to add the column) Then: UPDATE userTable SET upperName = upper(name) (which would update 'upperName' in all existing rows) Then: CREATE TRIGGER upperNameInsertTrigger AFTER INSERT OF name ON userTable FOR EACH ROW BEGIN UPDATE upperName SET upperName=upper(new.name); END And also: CREATE TRIGGER upperNameUpdateTrigger AFTER UPDATE OF name ON userTable FOR EACH ROW BEGIN UPDATE upperName SET upperName=upper(new.name); END Seems rather long winded though, and is effective duplication of data. Using a VIEW as Simon recommends seems a lot simpler, or just select the field as upper(name) when you want to get the data out. Nick. -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
