CODEWITHSUNDEEP
mysqlsqluser-defined-functions
illuzive
illuzive

Reputation: 349

Binding a function to a column in MySQL

I'd like to bind a MySQL function (UDF) to a column in a table, where the column would act as an alias for the function, and return its value.

A pseudo code example might explain this better than words.

TABLE example (
    ...
    col_x => int,
    col_y => int,
    col_sum => function() { return SUM(col_x + col_y}
);

What I want to be able to do is:

SELECT col_sum FROM example WHERE...;

A row where col_x = 3 and col_y = 5 should make SELECT col_sum return 8.

I know that it's really simple to do this in the query itself, but in this case I'm trying to fix a bug in a project without rewriting lots of existing code.

I'm currently using MySQL 5.5.

(I've tried to google this, but so far I'm just getting results about using UDFs in my queries, which doesn't help.)

Upvotes: 0

Views: 836

Answers (2)

StanislavL
StanislavL

Reputation: 57421

Add the column and a triggeer to fill the column on insert/update.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Can you use a view?

create view v_table as
    select (colx + coly) as colsum, t.*
    from "table" t;

One possibility is to actually rename the original table to something like _table, and then create a view with the original table name:

rename table "table" to "_table";

create view "table" as
    select (colx + coly) as colsum, t.*
    from "_table" t;

Upvotes: 3

Related Questions