ISO/IEC PRF 19075-7
(Main)Information technology -- Guidance for the use of database language SQL
Information technology -- Guidance for the use of database language SQL
Titre manque
General Information
Standards Content (sample)
INTERNATIONAL ISO/IEC
STANDARD 19075-7
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 7:
Polymorphic table functions
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-7:2021(E)
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-7:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved
---------------------- Page: 2 ----------------------
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---------------------- Page: 3 ----------------------
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---------------------- Page: 4 ----------------------
ISO/IEC19075-7:2021(E)
9.7.3
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---------------------- Page: 5 ----------------------
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---------------------- Page: 6 ----------------------
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---------------------- Page: 7 ----------------------
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---------------------- Page: 8 ----------------------
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---------------------- Page: 9 ----------------------
ISO/IEC19075-7:2021(E)
48 Score:secondargumentfullrowtypedescriptor...............................................113
49 Score:secondargumentpartitioningdescriptor.................
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.