0

Is it possible to pass an entire record as a parameter to a function, similarly to Postgres, in MySQL 8? If not, are there any alternatives?

The specific use cases are:

  • Define custom (record -> bool) filters to be reused in multiple queries
  • Define custom mappings (record -> JSON)

For the first use case (custom filters) an example would be converting a row of a table appointment:

id, user_id, begins_at, ends_at, is_something, cancelled_at

into this expression:

(
    is_something && (cancelled_at IS NULL && @someDate > begins_at && @someDate < ends_at)
        || !is_something && (@someDate > ends_at)
)

so that I can reuse this expression.

For the second use case the idea is given the same appointment to convert it into a specific JSON shape (MySQL 8) with subqueries and nested objects.

Shoe Diamente
  • 723
  • 1
  • 5
  • 24

1 Answers1

0

You cannot pass a row to a routine (function/procedure). You can use a temporary table and put the data in it before calling the routine. The temporary table will be visible in the routines as long as you declare it in same session.

If you describe the use case with more detail, it's easier to give a more detailed answer.

Here is a quess based on your sample data:

create function f_do_something(
in_is_something int, 
in_cancelled_at datetime,
in_some_date date,
in_begins_at datetime, 
in_ends_at datetime
)
returns int
begin

return (
  (in_is_something!=0 and 
   in_cancelled_at is null and 
   in_some_date>in_begins_at and
   in_some_date<in_ends_at)
  or (
   in_is_something=0 and
   in_some_date>in_ends_at
  )
);

end;

You can then use the function in your code

select *
from your data
where f_do_something(is_something, cancelled_at, '2019-12-17', begins_at, ends_at) > 0;
slaakso
  • 8,331
  • 2
  • 16
  • 27
  • The use cases are listed in the question, no? What other details do you need? Let's see if I can provide them. – Shoe Diamente Dec 13 '19 at 17:00
  • @ShoeDiamente You get better answers if you provide a question with sample data and expected result. Helps you as well. – slaakso Dec 13 '19 at 17:06
  • I don't think "sample data" and "expected results" strictly apply here. I'm asking about a general workaround for defining custom filters / custom mappings to JSON of a row. I've tried to extend the question with some examples. – Shoe Diamente Dec 17 '19 at 08:24
  • With generic questions you get generic answers as it is hard to quess exactly what you are trying to do. With the sample data, I'd say you define a function with parameters of those variables (columns in a row) that you need for your logic. – slaakso Dec 17 '19 at 14:01