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
Current Stage
6060 - International Standard published
Start Date
01-Sep-2021
Completion Date
01-Sep-2021
Ref Project

Buy Standard

Standard
ISO/IEC 19075-7:2021 - Information technology -- Guidance for the use of database language SQL
English language
170 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF 19075-7:Version 10-jul-2021 - Information technology -- Guidance for the use of database language SQL
English language
170 pages
sale 15% off
Preview
sale 15% off
Preview

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 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

......................................................................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
---------------------- 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:secon
...

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

......................................................................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
---------------------- 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.