How to create table-valued functions in Vertica SQL?

Question

Does anyone know how to create table-valued function in Vertica SQL?

For example, I want to create a function

f_student_name()

that takes in input: student_id

to return output: student_name

in a query, extracting directly from a students table, where student_id, student_name are unique 1:1 mapped.

Use case: not having to constantly join on the same table for a lot of queries, improving readability and reducing spaghetti code.

I can't seem to find any documentation on this. Function documentation in Vertica only gives examples of creating functions by hardcoding (CASE x when ... then ... )

I would like to build this table-valued function using VSQL, but an optimal solution using Python (and have the function work in Vertica) would also work.

Answer 1

Table-valued functions tend to create a lot of nested loops in a query plan. Vertica being a Big Data DBMS has a general architecture that tends to avoid anything that slows down a query plan.

Joins are not spaghetti code. They are part of the relational paradigm.

Come to think of it, spaghetti code as I know it refers to the process flow of a procedural or object-oriented language. You can create spaghetti code in anything from COBOL over Pascal to C and, indeed, Python, but not in the descriptive language that SQL constitutes ...

But to simplify code and make your queries even faster, try using a flattened column:

CREATE TABLE f_marks (
  mark_ts TIMESTAMP
, subject_id INT
, stud_id INT
, subject VARCHAR(32) 
  DEFAULT (SELECT subject FROM d_subject WHERE id=f_marks.subject_id)
, stud_name VARCHAR(32) 
  DEFAULT (SELECT stud_name FROM d_student WHERE id=f_marks.stud_id)
);

You insert into f_marks using:

INSERT INTO f_marks (
  mark_ts,subject_id,stud_id
) VALUES (
  CURRENT_TIMESTAMP,4321,123986
)

, and the two VARCHARs are populated by magic - and it's actually pretty performant.

A much cleaner approach, as far as I'm concerned ....

Here's the docu on it : https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm