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 I'information - Recommandations pour I'utilisation du langage de base de données SQL Partie 7: Fonctions de table polymorphes Reference number TEC IS0/IEC19075-7:2021(E) tso @IS0/IEC2021 IS0/IEC 19075-7:2021(E) COPYRIGHT PROTECTED DOCUMENT IS0/IEC2021 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:+4122 749 0111 Email: [email protected] Website:www.iso.org Published in Switzerland i @ IS0/IEC 2021 - All rights reserved IS0/IEC 19075-7:2021(E) Contents Page Foreword.. . xii Introduction. xiv 1 Scope. 2 Normative references. 3 Terms and definitions. 4 Introduction to polymorphic table functions.. 4.1 Context of polymorphic table functions. . 4.2 What is a polymorphic table function?.. 4.3 Audiences. . . 4.4 Motivating examples. . . 4.4.1 Summary of motivating examples. 4.4.2 CsVreader 4.4.3 Pivot. 4.4.4 Score. 4.4.5 TopNplus. 4.4.6 ExecR. 4.4.7 Similarity. 15 4.4.8 UDjoin. 4.4.9 MapReduce. 18 4.5 The life cycle of a PTF. 19 5 PTF processing model. .21 5.1 Introduction to the PTF processing model. 21 5.2 Processing phases. . 21 5.3 Virtual processors. . 5.4 PTF component procedures. . 5.5 Input table characteristics. . 5.6 Partitioning and ordering. 5.7 Flow of control. .. . 5.8 Flow of information. . ..25 5.9 Flow of row types. .26 5.10 Pass-through columns. 5.11 Security model. . . 28 5.12 Conformance features. 29 6 Specification.. .31 6.1 Introduction to the specification of PTFs. .31 6.2 Functional specification. . 31 6.2.1 Introduction to the functional specification. 31 6.2.2 Parameter list. 31 @ IS0/IEC 2021 - All rights reserved ii IS0/IEC 19075-7:2021(E) 6.2.3 Input table semantics. 32 6.2.4 Prunability. . 33 6.2.5 Pass-through columns. .33 6.2.6 Result row type. 6.2.7 Determinism. 6.2.8 SQL-data access. . 6.2.9 Documenting the PTF to the query author. . 6.3 Design specification. ... 6.3.1 Introduction to the design specification. 6.3.2 Name the component procedures. . 6.3.3 Private data... ..36 6.3.4 Routine characteristics of the component procedures. ..37 6.3.5 Component procedure signatures. . .38 7 Data definition language. . . .43 7.1 Introduction to data definition language. . . . .43 7.2 PTF creation..... .43 7.3 PTF component procedures. . .45 7.4 Altering PTF component procedures and PTFs. . . . 45 7.5 Dropping a PTF and its component procedures. . : 45 8 Implementation. .. .46 8.1 Introduction to PTF implementation.... . . 8.2 PTF descriptor areas. . . 8.2.1 Introduction to PTF descriptor areas. . . . 8.2.2 PTF descriptor area header. . . 47 8.2.3 SQL item descriptor areas for row types. 8.2.4 SQL item descriptor areas for partitioning. . ..51 8.2.5 SQL item descriptor areas for ordering. . .51 8.3 PTF extended names. . . .52 8.4 Reading a PTF descriptor area. . .52 8.5 Writing a PTF descriptor area. . 53 8.5.1 Introduction to writing a PTF descriptor area. . : .53 8.5.2 Using DESCRIBE to populate a PTF descriptor area. . . .53 8.5.3 Using SET DESCRIPTOR to populate a PTF descriptor area . : .54 8.5.4 Using COPY DESCRIPTOR to populate a PTF descriptor area . . 55 8.6 Reading a PTF input cursor. . . . .55 8.7 Outputting a row. . .56 9 Invocation. .58 9.1 Introduction to PTF invocation. . . . 9.2 <table primary>... . 9.3 <PTF derived tabie>. . 9.4 Proper result correlation name and proper result column naming. 9.5 <routine invocation>, 9.6 <table argu