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

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

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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

              Assignee:
              serg Sergei Golubchik
              Reporter:
              spaceelve Adrian Partl
              Votes:
              9 Vote for this issue
              Watchers:
              12 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.