Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5199

Table functions (a.k.a UDF returning arrays / result set)

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • None

    Description

      Idea

      User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example

      SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b

      We might (or might not) specify that all function arguments must be constants.

      UDF

      • It won't be a UDF, of course, but a new plugin type.
      • it'll use a generator interface

      SQL

      SQL Standard says

      this is my understanding of the standard. it might be wrong

      To define a table function one uses

      CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...])

      It should return a table expression, which pretty much means either

        RETURN SELECT ...

      or

        CREATE LOCAL TEMPORARY TABLE t1 (...)
        INSERT t1 VALUES (...)
        RETURN t1;

      and it's used as

      SELECT ... FROM TABLE(func(arg1, arg2, ...)) ...

      Optimizations

      Trivial function (with the body of only RETURN SELECT) can be treated as view. But I'm not sure it's a use case worth optimizing.

      Complex function can be changed from materializing to streaming if we can be sure that every inserted row is guaranteed to be returned. That is:

      • it uses exactly the second pattern above. CREATE/INSERT/RETURN.
      • CREATE and RETURN are not conditional.
      • No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc.
      • No dynamic SQL

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              spaceelve Adrian Partl
              Votes:
              13 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.