ISO/IEC 19075-7:2021

Information technology - Guidance for the use of database language SQL - Part 7: Polymorphic table functions

This document describes the definition and use of polymorphic table functions in SQL. The Report discusses the following features of the SQL Language: - The processing model of polymorphic table functions in the context of SQL. - The creation and maintenance of polymorphic table functions. - Issues related to methods of implementing polymorphic table functions. - How polymorphic table functions are invoked by application programs. - Issues concerning compilation, optimization, and execution of polymorphic table functions.

Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 7: Fonctions de table polymorphes

General Information

• Status: Published
• Publication Date: 31-Aug-2021
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 6060 - International Standard published
• Start Date: 01-Sep-2021
• Due Date: 11-Feb-2024
• Completion Date: 31-Aug-2021

Relations

• Revises: ISO/IEC TR 19075-7:2017 - Information technology - Database languages - SQL Technical Reports - Part 7: Polymorphic table functions in SQL
• Effective Date: 23-Apr-2020

Overview

ISO/IEC 19075-7:2021 provides formal guidance on polymorphic table functions (PTFs) in SQL. It explains how PTFs are defined, created, maintained, invoked and implemented within the SQL environment, and addresses compilation, optimization and execution concerns. The document is part of the ISO/IEC 19075 series that advises on advanced SQL language features and is aimed at implementers and advanced SQL users who design extensible table-producing routines.

Key Topics and Technical Requirements

The standard covers the full lifecycle and technical model for polymorphic table functions, including:

• PTF processing model: phases, virtual processors, flow of control, information and row types.
• Functional and design specification: parameter lists, input table semantics, result row types, determinism and prunability.
• Data Definition Language (DDL) for PTFs: creation, altering and dropping of PTFs and component procedures.
• Implementation details: PTF descriptor areas, descriptor formats for row types, partitioning and ordering metadata, reading/writing descriptors.
• Invocation semantics: how PTFs are used as table expressions, table arguments, nested invocations, partitioning, pruning and co-partitioning rules.
• Compilation, optimization and execution: invoking describe procedures, optimization opportunities, execution flow across virtual processors, start/fulfill/finish component procedures, and cleanup.
• Examples and use-cases: worked examples (Projection, CSVreader, Pivot, Score, TopNplus, MapReduce and more) illustrating practical implementations and invocation patterns.

Keywords: ISO/IEC 19075-7:2021, polymorphic table functions, SQL, PTF, processing model, partitioning, descriptor area, virtual processors.

Applications and Who Should Use This Standard

ISO/IEC 19075-7:2021 is targeted at:

• Database engine implementers building support for advanced table-valued and polymorphic functions.
• Query optimizer and compiler authors who need to reason about describe/compile/execute semantics for PTFs.
• SQL extension designers and language architects implementing DDL, invocation and security models for PTFs.
• Application developers and DBAs designing high-performance data-processing functions (e.g., CSV readers, pivots, Top-N algorithms, custom MapReduce patterns) inside the database.
• System integrators who require predictable behavior when embedding procedural or external row-generation into SQL queries.

PTFs enable powerful in-database processing patterns where table-producing routines adapt to input schemas, support partitioned parallelism and integrate with the SQL optimizer.

Related Standards

• ISO/IEC 19075 (other parts) - guidance on SQL advanced features.
• ISO/IEC 9075 (SQL) - the core international SQL standard that PTF guidance complements.

For implementers and architects working on extensible SQL functions, ISO/IEC 19075-7:2021 is a practical reference to ensure consistent, optimizable, and secure polymorphic table function behavior.

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-7
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 7:
Polymorphic table functions
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 7: Fonctions de table polymorphes
Reference number
©
ISO/IEC 2021
© ISO/IEC 2021
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved

ISO/IEC19075-7:2021(E)
Contents Page
Foreword.xii
Introduction.xiv
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Introductiontopolymorphictablefunctions.4
4.1 Contextofpolymorphictablefunctions.4
4.2 Whatisapolymorphictablefunction?.4
4.3 Audiences.4
4.4 Motivatingexamples.5
4.4.1 Summaryofmotivatingexamples.5
4.4.2 CSVreader.5
4.4.3 Pivot.6
4.4.4 Score.8
4.4.5 TopNplus.11
4.4.6 ExecR.14
4.4.7 Similarity.15
4.4.8 UDjoin.17
4.4.9 MapReduce.18
4.5 ThelifecycleofaPTF.19
5 PTFprocessingmodel.21
5.1 IntroductiontothePTFprocessingmodel.21
5.2 Processingphases.21
5.3 Virtualprocessors.21
5.4 PTFcomponentprocedures.21
5.5 Inputtablecharacteristics.22
5.6 Partitioningandordering.23
5.7 Flowofcontrol.24
5.8 Flowofinformation.25
5.9 Flowofrowtypes.26
5.10 Pass-through columns.27
5.11 Security model.28
5.12 Conformancefeatures.29
6 Specification.31
6.1 IntroductiontothespecificationofPTFs.31
6.2 Functionalspecification.31
6.2.1 Introductiontothefunctionalspecification.31
6.2.2 Parameter list.31
©ISO/IEC2021–Allrightsreserved iii

ISO/IEC19075-7:2021(E)
6.2.3 Inputtablesemantics.32
6.2.4 Prunability.33
6.2.5 Pass-through columns.33
6.2.6 Resultrowtype.34
6.2.7 Determinism.34
6.2.8 SQL-dataaccess.35
6.2.9 DocumentingthePTFtothequeryauthor.35
6.3 Design specification.36
6.3.1 Introductiontothedesignspecification.36
6.3.2 Namethecomponentprocedures.36
6.3.3 Privatedata.36
6.3.4 Routinecharacteristicsofthecomponentprocedures.37
6.3.5 Componentproceduresignatures.38
7 Datadefinitionlanguage.43
7.1 Introductiontodatadefinitionlanguage.43
7.2 PTF creation.43
7.3 PTFcomponentprocedures.45
7.4 AlteringPTFcomponentproceduresandPTFs.45
7.5 DroppingaPTFanditscomponentprocedures.45
8 Implementation.46
8.1 IntroductiontoPTFimplementation.46
8.2 PTFdescriptorareas.46
8.2.1 IntroductiontoPTFdescriptorareas.46
8.2.2 PTFdescriptorareaheader.47
8.2.3 SQLitemdescriptorareasforrowtypes.48
8.2.4 SQLitemdescriptorareasforpartitioning.51
8.2.5 SQLitemdescriptorareasforordering.51
8.3 PTFextendednames.52
8.4 ReadingaPTFdescriptorarea.52
8.5 WritingaPTFdescriptorarea.53
8.5.1 IntroductiontowritingaPTFdescriptorarea.53
8.5.2 UsingDESCRIBEtopopulateaPTFdescriptorarea.53
8.5.3 UsingSETDESCRIPTORtopopulateaPTFdescriptorarea.54
8.5.4 UsingCOPYDESCRIPTORtopopulateaPTFdescriptorarea.55
8.6 ReadingaPTFinputcursor.55
8.7 Outputtingarow.56
9 Invocation.58
9.1 IntroductiontoPTFinvocation.58
9.2 .58
9.3 .58
9.4 Properresultcorrelationnameandproperresultcolumnnaming.58
9.5 .59
9.6 .60
9.7 .60
9.7.1 Typesoftablearguments.60
9.7.2 .61
iv ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
9.7.3

.61
9.7.4 Nestedtablefunctioninvocation.62
9.8 Tableargumentcorrelationname.62
9.9 Tableargumentcolumnrenaming.62
9.10 RangevariablesandcolumnrenaminginnestedPTF.63
9.11 Partitioning.63
9.12 Pruning.64
9.13 Ordering.64
9.14 Co-partitioning.65
9.15 Crossproductsofpartitions.66
9.16 .66
10 Compilation.67
10.1 IntroductiontoPTFcompilation.67
10.2 Callingthedescribecomponentprocedure.67
10.3 Insidethedescribecomponentprocedure.67
10.4 Usingtheresultofdescribe.68
11 Optimization.69
12 Execution.70
12.1 IntroductiontoPTFexecution.70
12.2 Partitionsandvirtualprocessors.70
12.3 Callingthestartcomponentprocedure.71
12.4 Insidethestartcomponentprocedure.71
12.5 CallingthePTFfulfillcomponentprocedure.72
12.6 InsidethePTFfulfillcomponentprocedure.72
12.7 Closing cursors.72
12.8 CallingthePTFfinishcomponentprocedure.72
12.9 InsidethePTFfinishcomponentprocedure.73
12.10 Collectingtheoutput.73
12.11 Cleanuponavirtualprocessor.73
12.12 Finalresult.73
13 Examples.74
13.1 Introductiontotheexamples.74
13.2 Projection.75
13.2.1 Overview.75
13.2.2 FunctionalspecificationofProjection.75
13.2.3 DesignspecificationforProjection.76
13.2.4 Projectioncomponentprocedures.76
13.2.5 InvokingProjection.78
13.2.6 Calling Projection_describe.78
13.2.7 InsideProjection_describe.80
13.2.8 ResultofProjection_describe.82
13.2.9 VirtualprocessorsforProjection.82
13.2.10 CallingProjection_fulfill.83
13.2.11 InsideProjection_fulfill.84
13.2.12 Collectingtheresults.84
13.2.13 Cleanup.85
©ISO/IEC2021–Allrightsreserved v

ISO/IEC19075-7:2021(E)
13.3 CSVreader.86
13.3.1 Overview.86
13.3.2 FunctionalspecificationofCSVreader.86
13.3.3 DesignspecificationforCSVreader.86
13.3.4 CSVreadercomponentprocedures.87
13.3.5 ImplementationofCSVreader.88
13.3.6 Invoking CSVreader.88
13.3.7 Calling CSVreader_describe.89
13.3.8 InsideCSVreader_describe.91
13.3.9 ResultofCSVreader_describe.92
13.3.10 VirtualprocessorforCSVreader.94
13.3.11 Calling CSVreader_start.94
13.3.12 InsideCSVreader_start.95
13.3.13 CallingCSVreader_fulfill.95
13.3.14 Inside CSVreader_fulfill.95
13.3.15 Collectingtheoutput.96
13.3.16 CallingCSVreader_finish.96
13.3.17 Inside CSVreader_finish.96
13.3.18 Cleanup.97
13.4 Pivot.98
13.4.1 Overview.98
13.4.2 FunctionalspecificationofPivot.98
13.4.3 DesignspecificationforPivot.98
13.4.4 Pivotcomponentprocedures.99
13.4.5 InvokingPivot.100
13.4.6 CallingPivot_describe.100
13.4.7 Inside Pivot_describe.104
13.4.8 ResultofPivot_describe.105
13.4.9 VirtualprocessorsforPivot.107
13.4.10 Calling Pivot_fulfill.108
13.4.11 InsidePivot_fulfill.108
13.4.12 Collectingtheresults.109
13.4.13 Cleanup.109
13.5 Score.110
13.5.1 Overview.110
13.5.2 FunctionalspecificationofScore.110
13.5.3 DesignspecificationforScore.110
13.5.4 Scorecomponentprocedures.111
13.5.5 InvokingScore.112
13.5.6 Calling Score_describe.112
13.5.7 InsideScore_describe.115
13.5.8 ResultofScore_describe.116
13.5.9 VirtualprocessorsforScore.117
13.5.10 CallingScore_fulfill.119
13.5.11 InsideScore_fulfill.119
13.5.12 Collectingtheoutput.120
13.5.13 Cleanup.121
vi ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
13.6 TopNplus.122
13.6.1 Overview.122
13.6.2 FunctionalspecificationofTopNplus.122
13.6.3 DesignspecificationforTopNplus.122
13.6.4 TopNpluscomponentprocedures.123
13.6.5 Invoking TopNplus.124
13.6.6 CallingTopNplus_describe.124
13.6.7 Inside TopNplus_describe.127
13.6.8 ResultofTopNplus_describe.129
13.6.9 VirtualprocessorsforTopNplus.129
13.6.10 Calling TopNplus_fulfill.131
13.6.11 InsideTopNplus_fulfill.131
13.6.12 Collectingtheoutput.132
13.6.13 Cleanup.132
13.6.14 TopNplususingpass-throughcolumns.132
13.7 ExecR.135
13.7.1 Overview.135
13.7.2 FunctionalspecificationofExecR.135
13.7.3 DesignspecificationforExecR.135
13.7.4 ExecRcomponentprocedures.136
13.7.5 Invoking ExecR.137
13.7.6 CallingExecR_describe.137
13.7.7 Inside ExecR_describe.139
13.7.8 ResultofExecR_describe.140
13.7.9 VirtualprocessorsforExecR.140
13.7.10 CallingExecR_start.141
13.7.11 InsideExecR_start.141
13.7.12 Calling ExecR_fulfill.142
13.7.13 Inside ExecR_fulfill.142
13.7.14 Collectingtheoutput.142
13.7.15 Calling ExecR_finish.143
13.7.16 InsideExecR_finish.143
13.7.17 Cleanup.143
13.8 Similarity.144
13.8.1 Overview.144
13.8.2 FunctionalspecificationofSimilarity.144
13.8.3 DesignspecificationforSimilarity.144
13.8.4 Similaritycomponentprocedures.145
13.8.5 InvokingSimilarity.145
13.8.6 CallingSimilarity_describe.146
13.8.7 Inside Similarity_describe.148
13.8.8 ResultofSimilarity_describe.149
13.8.9 VirtualprocessorsforSimilarity.149
13.8.10 Calling Similarity_fulfill.153
13.8.11 InsideSimilarity_fulfill.153
13.8.12 Collectingtheoutput.154
13.8.13 Cleanup.154
©ISO/IEC2021–Allrightsreserved vii

ISO/IEC19075-7:2021(E)
13.9 UDjoin.155
13.9.1 Overview.155
13.9.2 FunctionalspecificationofUDjoin.155
13.9.3 DesignspecificationforUDjoin.155
13.9.4 UDjoincomponentprocedures.156
13.9.5 Invoking UDjoin.156
13.9.6 CallingUDjoin_describe.157
13.9.7 InsideUDjoin_describe.157
13.9.8 ResultofUDjoin_describe.158
13.9.9 VirtualprocessorsforUDjoin.158
13.9.10 CallingUDjoin_fulfill.158
13.9.11 Inside UDjoin_fulfill.159
13.9.12 Collectingtheoutput.159
13.9.13 Cleanup.159
13.10 NestedPTFinvocation.160
13.10.1 IntroductiontonestedPTFinvocation.160
13.10.2 NestedPTFsyntaxandsemantics.160
13.10.3 NestedPTFcompilation.162
13.10.4 NestedPTFexecution.164
13.10.5 ThePTFauthor’sviewofnestedPTFinvocations.165
Bibliography.166
Index.167
viii ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
Tables
Table Page
1 ResultsofCVSreadersamplequery.6
2 Sampledataforpivotexample.8
3 Pivotexampleresultwithsampledata.8
4 Scoreexample:contentsofModeltable.10
5 Scoreexample:datatobescored.10
6 Scoreexample:result.10
7 TopNplusexample:inputdata.12
8 TopNplusexample:Eastinputdata.12
9 TopNplusexample:Westinputdata.12
10 TopNplusexample:result.13
11 TopNplusexample:resultwithstatistics.14
12 Similarityexample:result.17
13 UDjoinexample:result.18
14 PrimaryaudiencesforClausesandSubclausesinthisdocument.19
15 Examplesandtableparametercharacteristics.23
16 Schematicexecutionplan.24
17 PTFroutinecharacteristics.37
18 Tableparametersemantics.39
19 CorrespondingPTFcomponentprocedureparameters.40
20 PTFdescriptorarea.47
21 PTFdescriptorareaheader.47
22 RelevantSQLitemdescriptorcomponents.48
23 Scenariosillustratedbytheexamples.74
24 Derivingparameternames.77
25 DescriptorforEmp’srowtype.78
26 Inputtable’srequestedrowtype.79
27 Descriptorgeneratedfromquery’sargument.79
28 Descriptorofinitialresultrow.80
29 Populatedinputrequestdescriptor.82
30 Populatedinitialresultrowdescriptor.82
31 Inputcursor’srowtypedescriptor.83
32 PTFdescriptorareaforfloats.90
33 PTFdescriptorareafordates.90
34 PTFdescriptorareaforinitialresult.90
35 PTFdescriptorareaafterdescribeofinitialresult.92
36 Rowtypecorrespondingtodescriptor.93
37 Pivot:rowtypedescriptor.101
38 Pivot:requestedrowtypedescriptor.102
39 Pivot:descriptorforfirstargument.102
40 Pivot:descriptorforsecondargument.103
41 Pivot:descriptorforthirdargument.103
42 Pivot:initialrowtypedescriptor.104
43 Pivot:requestedrowtypedescriptor.106
44 Pivot:initialresultrowtypedescriptor.106
45 Pivot:columnsoftheresultrowtype.107
46 Score:firstargumentfullrowtypedescriptor.112
47 Score:firstargumentrequestedrowtypedescriptor.113
©ISO/IEC2021–Allrightsreserved ix

ISO/IEC19075-7:2021(E)
48 Score:secondargumentfullrowtypedescriptor.113
49 Score:secondargumentpartitioningdescriptor.114
50 Score:secondargumentorderingdescriptor.114
51 Score:initialresultrowtypedescriptor.115
52 Score:firstinputtablerequestedrowdescriptors.116
53 Score:secondinputtablerequestedrowdescriptors.116
54 Score:resultrowtype.117
55 Score:secondinputtabledata.117
56 Score:firstinputtabledata.118
57 Score:virtualprocessorcursors.118
58 Score:fullrowtypedescriptor.125
59 Score:partitioningdescriptor.126
60 Score:orderingdescriptor.126
61 Score:requestedrowtypedescriptor.126
62 Score:intermediaterowtypedescriptor.127
63 Score:completeresultrowtype.129
64 Wcore:sampledata.129
65 ExecR:inputtablepartitioningdescriptor.138
66 ExecR:fifthdescriptor.138
67 ExecR:sixthdescriptor.139
68 ExecR:resultrowtypedescriptor.140
69 Similarity:fullrowtypedescriptorforInput1.146
70 Similarity:partitioningdescriptorforInput1.147
71 Similarity:orderingdescriptorforInput1.147
72 Similarity:resultrowtype.149
73 Similarity:queryresult.149
74 Similarity:masterlistqueryresult.151
75 Similarity:masterlistqueryresultwithoutco-partitioning.152
76 Similarity:completeoutput.154
77 UDjoin:resultrowtype.158
78 NestedPTFpositedPTFs.160
x ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
Figures
Figure Page
1 PTFinformationflow.26
2 Rowtyperelationships.27
3 NestedPTFdataflow.161
4 Flowofrowtypes.163
5 Simplifiedflowofrowtypes.164
6 Neteffectofcompletecompilation.164
©ISO/IEC2021–Allrightsreserved xi

ISO/IEC19075-7:2021(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seepatents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.IntheIEC,seewww.iec.ch/understanding-standards.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-7cancelsandreplacesISO/IECTR19075-7:2017.
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC
9075series:
— ISO/IEC9075-1,sixtheditionorlater;
— ISO/IEC9075-2,sixtheditionorlater;
— ISO/IEC9075-3,sixtheditionorlater;
— ISO/IEC9075-4,seventheditionorlater;
— ISO/IEC9075-9,fiftheditionorlater;
— ISO/IEC9075-10,fiftheditionorlater;
— ISO/IEC9075-11,fiftheditionorlater;
— ISO/IEC9075-13,fiftheditionorlater;
— ISO/IEC9075-14,sixtheditionorlater;
— ISO/IEC9075-15,secondeditionorlater;
— ISO/IEC9075-16,firsteditionorlater.
xii ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
©ISO/IEC2021–Allrightsreserved xiii

ISO/IEC19075-7:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Introductiontopolymorphictablefunctions”,providesanintroductiontopolymorphic
tablefunctions,therequirementsleadingtotheirincorporationintoSQL,andillustrationsoftheir
use.
5) Clause5,“PTFprocessingmodel”,describestheabstractprocessingmodelforpolymorphictable
functionsinthecontextofanSQL-implementation.
6) Clause6,“Specification”,describesthemannerinwhichpolymorphictablefunctionsarespecified
intheSQLstandard.
7) Clause7,“Datadefinitionlanguage”,providesthesyntaxandsemanticsoftheSQLstatementsthat
create,modify,anddroppolymorphictablefunctions.
8) Clause8,“Implementation”,guidesauthorsofpolymorphictablefunctionsthroughthestepsrequired
tocreateallofthefunctionsnecessarytoaccomplishparticularpurposes.
9) Clause9,“Invocation”,suppliestheinformationnecessaryforapplicationwriters,especiallySQL
queryauthors,totakeadvantageofthepolymorphictablefunctionsthatareavailabletothem.
10)Clause10,“Compilation”,isdirectedattheauthorsofpolymorphictablefunctionsandofSQLdatabase
systemstoguidetheminthestepsrequiredtocompilepolymorphictablefunctionsinthecontext
ofaparticularSQL-implementation.
11)Clause11,“Optimization”,describesthevariousaspectsofpolymorphicfunctionsofwhichthe
authorsofsuchfunctionsandtheauthorsofSQL-implementationsmustbeawaretoadequately
optimizetheexecutionofsuchfunctions.
12)Clause12,“Execution”,discussesthedetailsofexecutingpolymorphictablefunctionsinthecontext
oftheprocessingmodel.
13)Clause13,“Examples”,suppliesnumerousexamplesindetailwithcommentariestoexplainthe
varioususecases,therequirementsthatrelatetopolymorphictablefunctions,andthespecificsof
thesolutionsforeachusecase.
xiv ©ISO/IEC2021–Allrightsreserved

INTERNATIONAL STANDARD ISO/IEC 19075-7:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part7:
Polymorphictablefunctions
1 Scope
ThisdocumentdescribesthedefinitionanduseofpolymorphictablefunctionsinSQL.
TheReportdiscussesthefollowingfeaturesoftheSQLLanguage:
— TheprocessingmodelofpolymorphictablefunctionsinthecontextofSQL.
— Thecreationandmaintenanceofpolymorphictablefunctions.
— Issuesrelatedtomethodsofimplementingpolymorphictablefunctions.
— Howpolymorphictablefunctionsareinvokedbyapplicationprograms.
— Issuesconcerningcompilation,optimization,andexecutionofpolymorphictablefunctions.
©ISO/IEC2021–Allrightsreserved 1

ISO/IEC19075-7:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
2 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1apply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
©ISO/IEC2021–Allrightsreserved 3

ISO/IEC19075-7:2021(E)
4 Introductiontopolymorphictablefunctions
4.1 Contextofpolymorphictablefunctions
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-2.
4.2 Whatisapolymorphictablefunction?
Apolymorphictablefunction(abbreviatedPTF)isafunctionthatreturnsatablewhoserowtypeisnot
declaredwhenthefunctioniscreated.Rather,therowtypeoftheresultmaydependonthefunction
argumentsintheinvocationofaPTF,andthereforemayvarydependingontheprecisesyntaxcontaining
thePTFinvocation.Inaddition,aPTFmayhavegenerictableparameters(i.e.,,norowtypedeclared
whenthePTFiscreated),andtherowtypeoftheresultmightdependontherowtype(s)oftheinput
tables.ThisdocumentisintendedtoprovideaninformaldescriptionofPTFs,usingexamplesandpractical
step-by-stepadviceonhowtoaddaPTFcapabilitytoarelationalDBMS,howtowriteaPTF,andhow
toinvokeaPTFinanapplication.
4.3 Audiences
Thisdocumentiswrittenforthreeaudiences:
1) TheDBMSdeveloper.
2) ThePTFauthor.
3) Thequeryauthor.
ItisimportanttorecognizethataPTFissomewhatlikeaview,onlymoreprocedural.Withaview,there
arethesamethreeparties:DBMS,viewauthor,andqueryauthor.TheDBMSistheintermediarybetween
theviewauthorandthequeryauthor.Theviewisawayfortheviewauthorto“publish”aninterfaceto
tableswithoutexposingtheinnerworkingsoftheinterface.Similarly,aPTFisawayforthePTFauthor
topublishaninterfacetoaproceduralmechanismthatdefinesatable.Thequeryauthoronlyseesthe
publishedinterface,whereastheDBMSandthePTFauthorshareamorecomplex“private”interface.In
particular,thequeryauthorseesasinglePTFfunction,whereastheDBMSandthePTFauthorseea
familyofonetofourrelatedSQL-invokedprocedures,calledthePTFcomponentprocedures,andpossibly
additionalprivatedata.
Manysectionsofthisdocumentbeginwithacaption“Primaryaudience:xyz”statingwhichaudienceis
mostlikelytofindthesectionuseful.However,anyoneiswelcometoreadanysection.Understanding
thecompletepicturefromallthreeperspectivesislikelytobehelpfultoallthreeaudiences.
Afourthaudiencecanalsobedistinguished,thedatabaseadministrator(DBA)astheonewhois
responsibleforallocatingdiskstorageforlargetables.Thisisbecausediskallocationstrategiescan
impacttheperformanceofqueries.Theoneallocatingdiskstoragemaybethequeryauthorormaybe
aseparateroleintheorganization.Iftheseareseparateroles,theDBAmayneedtoconsultwiththe
queryauthorbeforethedataisloaded.Thisisacomplexissuebecauseitisnotalwayspossibletoantic-
ipatethequeryatthetimethatthedataisloaded,orthesetofqueriesmaybesuchthatnoonedisk
4 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-7:2021(E)
4.3 Audiences
allocationschemewillallowoptimalperformanceofallqueries.ThemostrelevantsectionforaDBA
allocatingdiskstorageisSubclause12.2,“Partitionsandvirtualprocessors”,wheretheaudienceis
nominallytheDBMSandthePTFauthor.However,specificadviceaboutdiskallocationisbeyondthe
scopeofthisdocument.
4.4 Motivatingexamples
4.4.1 Summaryofmotivatingexamples
ThisdocumentuseseightmotivatingexamplesthatillustratethecapabilitiesofPTFs.Theseexamples
arepresentedfromthestandpointofthequeryauthor,hidingtheroleofthePTFauthorandDBMS.The
objectivehereistogetatasteofthepowerandgeneralityofPTFs.TheperspectivesoftheDBMSand
thePTFauthorareexploredatlengthinClause13,“Examples”.
4.4.2 CSVreader
Aspreadsheetcanusuallyoutputacomma-separatedlistofvalues.Generally,thefirstlineofthefile
containsalistofcolumnnames,andsubsequentlinesofthefilecontaindata.Thedataingeneralcanbe
treatedasalargeVARCHAR.However,someofthefieldsmaybenumericordatetime.
ThePTFauthorhasprovidedaPTFcalledCSVreaderdesignedtoreadafileofcomma-separatedvalues
andinterpretthisfileasatable.ThequeryauthorcanseethisPTFintheInformationSchemaandknows
thatithasthefollowingsignature:
FUNCTION CSVreader (
File VARCHAR(1000),
Floats DESCRIPTOR DEFAULT NULL,
Dates DESCRIPTOR DEFAULT NULL )
RETURNS TABLE
NOT DETERMINISTIC
CONTAINS SQL
ThissignaturehastwoparametertypesthataredistinctivetoPTFs:
1) DESCRIPTORisatypethatiscapableofdescribingalistofcolumnnames,andoptionallyforeach
columnname,adatatype.Thereisahelperfunctionprovidedforthequeryauthortoconstructa
PTFdescriptorarea.
2) TABLEdenotesthegenerictabletype,atypewhosevalueisatable.Therowtypeofthetableisnot
specified,andmayvarydependingontheinvocationofthePTF.
Inthisexample,thereturntypeofCSVreaderisTABLE.Thisisadistinguishingcharacteristicofevery
polymorphictablefunction:itreturnsagenerictable.
ThePTFauthorhaspublishedauserreferenceforCSVreader.Theuserreferencetellsthequeryauthor
thesemanticsoftheinputparametersandwhattheoutputwillbe.Inthisexample,the
...