 Welcome to today's Postgres conference webinar, CRUD Functions Generator Tool. We're joined by Javier Arau, database specialist, and Brenda Fierro-Servantes, Deputy Director of Information Technologies of Pemex, who will discuss CRUD operations and functions in database applications, a tool which leverages PLPGSQL to generate essential CRUD functions and how this tool aids PostgresQL's developers in standardized encoding style in order to simplify the time it takes to analyze cable structures. My name's Lindsay Hooper. I'm one of the Postgres conference organizers and you're moderated for this webinar a little bit about Pemex and FOSS. So at Pemex, the IT group aims to promote enabling capabilities platforms and open source solutions, encouraging technological development to respond adequately to the specific needs outlined in Pemex. Welcome to our speakers. With that, I'm gonna hand it off to Javier and Brenda. Take it away. Hi, everyone. My name is Javier. I'm speaking on behalf of my friends and colleagues of the FOSS group in Petrolo, Mexicans. This session is very special to us. It is our first participation in the Postgres conference webinar series. We are very glad to be here. The CRUD functions generator tools agenda considers the following aspects, purpose and scope. In the second point, we will focus on the tool features, install elements and use cases. In the third point, we will describe the configuration and execution procedures. Finally, we are going to have a demo. You will probably receive by email a zip file called webinar.zip with a user guide, install script and file samples of the tool for your evaluations. So let's begin. The purpose of this webinar is to share a tool to produce CRUD functions for Postgres tables with a standard coding style in a minimum time. The CRUD tool works with Postgres tables, identified by primary key fields and supporting foreign key constraints and produces functions in PLPG SQL for the following use cases. The first case is data create. This tool provides a function base on the insert statement with the proper fields of the table. Use case read. This will deliver a read function which select fields from table statement. If the table has foreign key constraints, the tool includes the associated tables and field relationships using the join-on syntax. The tool offers a variant of the read function and adds the where clause to restrict the record selection by key values. Use case update. The tool builds a function with an update statement using the key fields to restrict the update operation. Use case delete. The tool suggests the function with a delete statement using key values to limit the delete operation. Finally, the tool also generates a create-use statement with the similar features of the read function. By the end of the session, you will have a tool written in PLPG SQL to produce elemental code functions for PLPG SQL tables. You will learn how to use the tool to generate code functions in PG admin. You will understand the program logic behind the tool. You will identify the features supported in each use case of code function and you will recognize the benefits of the code tool in time saving and the standard coding practice. The code function generator tool is a contribution of the first group of petrodomichicanos to supposedly SQL community. So let's talk about the tool features. The script init underscore take you underscore function that SQL install three elements. By the way, these scripts on all the files including the presentation and the user guides are included in a zip file called webinar which you may probably receive in your email. So the three elements installed by the tool are the table name take you underscore tabla which is this table is used by the crew tool to identify which schema and tables are going to be processed. The result script will be stored in a column named gion. The second element of the installation is a function called take you underscore logic. This is the main function of the tool. What it does is it scans the take your tabla and calls another function, an auxiliary function name take you underscore genera underscore function to build crowd functions for each table marked to process. And the third element is a function called take you underscore genera underscore function with two arguments schema and table. It uses falseware SQL sectionaries to obtain stable attributes and types. Primary keys, foreign key constraints to infer associated tables and field relationships. These functions combines tables metadata with prototypes to produce the functions. This diagram summarizes the tool integration right here we have a function called take your logic which scans this table take your tablas for each table mark to process. There is a column in this table which is called processor. If it has a flag of one, it is indicated that it will be success. And also this function, when the function identifies a table to process, what it does is it invokes a function called take your genera underscore function to obtain the tables metadata and combines it with prototypes to produce the functions. This will be stored in the field name called yon which is this field over here. And we have here one of the most important tools about the tool is the information of the database engine. The metadata of the tables are included in this tables. Columns table underscore constraints key underscore column usage constraint column usage from the information schema and PG class PG underscore class PG underscore main space from the schema PG catalog. These tables, this system tables are used by the tool to obtain the metadata. Once the metadata is obtained, it combines it with group prototypes of these functions of these use cases and when the functions are included they are placed in this field. Okay, to illustrate the Cloud Function Generation process we are going to use three related tables. The first table we are going to use is one called pavement. The pavement table has a primary key defined by these two fields pay underscore level and pay underscore human. The next table we're going to use is one called departments. This table has a primary key field which is called step underscore ID. The third table we are going to use is the employee's table. This table uses MPV field as a primary key. This is the place where it's specified. And also this field has a serial type. In this type the values are generated by the sequence which is maintained by the database engine. In this case the user doesn't need to insert the values that is they are out of feed by the database. The second element of this table is the inclusion of two fields pay level and pay journal. These two fields work as foreign keys of the corresponding fields pay underscore level and pay journal of the pavement table. That is we have a foreign key relationship established in this table. We have another relationship and it is established with the table departments. Right here we have the field and we have the constraint definition to define that relationship. One thing to notice for the tool to recognize this foreign keys requires the fields of both tables. The table to possess and the reference tables must have the same names. Right here we have pay underscore level, pay underscore level, pay underscore journal, pay underscore journal, depth ID and depth ID. In this employee tables we have a pair of fields called insert underscore user and insert underscore date. These fields are used to store who inserts and the date of time of insertion for auditing for log reasons. The same thing happens with another pair of fields is the update underscore user and the update underscore date. In this table we want to register the user who updates and the date time of update. Okay, this sample data model is going to be used in the use cases so we can have a more precise idea of what the tool does. Okay, so let's review the use cases of the functions of the cloud function generation tool. The first use case is the create. So use case read function, no use case create function fix function, this function identifies the serial types fields. That is small serial, serial, big serial of the table structures. And what it does is excludes them from the fields values to insert. The field related to update are omitted from the list of fields values to insert, which are referenced by nombrecampo underscore usuario modifica and nombrecampo underscore fecha modifica. They are excluded from the list because in the insert operation it doesn't make sense to update, to register those fields. And the value of the current time step is assigned to the field reference in nombrecampo underscore fecha. We will review these features with an example. It's okay, we have the example for the create use case, the insert. The convention use to name the function is the prefix fn underscore table name underscore alta, which means append and the list of arguments. Right here we have the structure of the table employees. The MPID M underscore ID is the primary key field and we have two fields, the update underscore user and the update underscore date. And the insert that the two builds, what we have is the update user and an update date are not incorporated. And the MPID because it is a serial type is also excluded from the list because the database engine produces will generate this value. So for the use case of insert, the MPID serial types, sorry, these serial types are excluded. And if you have defined fields for the update and date time for the same operational update are also excluded. And if you ever define a field to differentiate the date time of insert, in this example insert underscore date, the build, the tool will include this value so you won't have to worry about the definition. And in the next slide, we have an example of the clock functions generated by the tool. We have the create or replace function command, the schema and using the convention we mentioned before, fn underscore the table name underscore alta. Right here, the tool includes the parameters using the prefix v underscore and the name of the field and uses the type of field register of the table to complete the definition. And then we have the insert statement. We have the fields. Please notice that in this definition, we have the insert user, insert date. Insert date is not included at the parameter list because it won't be necessary for you as a programmer to pass a value to this field. This field, the insert date, that is the current time stamp is passed automatically. And if the operation succeeds, it will return zero. And if an exception occurred, it will return minus one. This is the first case of the tool, the create function. The next use case is read loyalty one. It uses a functionality of selecting records by pages. The convention that in this field is fn underscore table name underscore cons with the argument list. What are the features? This function supports selects from a table and related tables at the first level only using foreign key constraints. Through those constraints, reference tables and field relationships are identified. The names of the foreign key fields of the table and the reference fields must be identical so the tool can relate them. The select clause includes the attributes of the associated tables. The function contains two parameter options, limit and offset to emulate the page in the browsing by pages. And the select clause shows the total number of records in the table. The field is called total underscore registers. The estimate of the total records in the table is obtained in execution time through the explain tool or using the table PG class to obtain the estimated records of the table. There is a nice article about this procedure. We have placed the reference in this slide. So if you could review what are the considerations about using the explain tool or using the PG class. Let's see an example of this use case. Right here we have the same structure of the employee's table. We have illuminated the section constraints. Right here we have a constraint with the department table and in this other section, we have identified the foreign key relationships with the table of pages. These functions, these concepts are used by the tool to express these relationships. We're using from the employee ID, from the employee using the LSP and using the left join on table on file relationships. This relationship is obtained by the metadata. One of the examples of the SIP file includes the query to obtain these relationships. Another thing to notice, because this table uses a relationship to view another fields of another tables in this function case, what the tool does is to include the fields of the related tables, in this case, payments, but it uses the prefix of the table to differentiate the full names of the related table versus the field that is included in the table. We are doing this, we are adding the prefix of the table to avoid full names duplicate. For instance, we have a field called pay underscore level because we are including in the list, in the select list to avoid duplicate. We are adding the payment, in this case, the table as a prefix to have payments underscore underscore paid journal. The same thing happens with the another table which is related, which is depart. We are using the table as a prefix to differentiate the related fields that are related attributes of the another table and differentiate them from the fields of the table that is processed. Also, in the function, we are including this statement. In order to obtain the total numbers of records of the table, we are using by default the functionality of the explained plan method. If you hit with explain and the, in this case, the select start from demo employees, what the explained will return is an explained plan. In that explained plan, it will be included the number of rows. To obtain the number of rows, we can use the display with the format of JSON. So in this variable, in this JSON variable, we can recover the total rows. And this method is very fast, but it's an estimation. And alternatively, we have another method for obtaining the total of records. By using the name of a call table named PG underscore class. PG underscore class has a column name, rail totals, which indicates the number of records of a table. This table is updated regularly. And if you hit the analyze, analyze verbose of the table, the records, the statistics are very fast. The records, the statistics are updated, including the name of the mouth, including the total records of the table. These two methods are included in the function. So you could use whatever you wish. And this is the function defined with the syntax of create a replace function using the convention fn underscore, the name of the table underscore cons. Right here, we can see that the, in the return table, there are defined the fields of the table. And in the selected statement, we are using the different attributes of the table. The table to process, we have an example of the read one use case using the select statement. In this function, we have the create a replace function using the convention fn underscore, the table name and underscore cons. We are using two arguments, limit and offset that will be used to paginate the results of the query. In the return tables, the tool generates the list of the fields of the table and only includes the fields of the table process and the fields of the reference tables. It also has a section where the function obtains the total records of the tape using the execute function, which calls the explain plan of the process to analyze the tables, analyze queries and alternatively, it includes another method to obtain the number of records using the table PG underscore classes. The third use case is read modality two, which is basically the same features of the flip function with the following exceptions. The key fields are included in the work class to restrict record selection and the estimated number of records of the table, limit and offset are excluded. Right here we have the function defined using the convention fn underscore employees underscore cons underscore jab will essentially have the same fields of the red function number one with the exception that we are including the work class to consider the key values of the table. The next use case is the use case update. In this function, the key fields are used in the work class to restrict the update operation. Key attributes are excluded from the set list. By definition or by practice, the key field values shouldn't be updated. According to that rule, we are excluding the key attributes from the set list. The fields related to insertion are omitted from the set list, which are the references by number campo underscore usuario alta and number campo underscore fetch alt. The value of the current sign step is assigned to the field reference in number campo underscore fetch a modifica. This is the structure of the table employees. Right here we have the update user and update date as fields for storing the date and the user who does the update operation. And right here, we have the update sentence built. In the set, we are excluding the key field and we are including the update user and the update date. And for obvious reasons, we are excluding the insert user and the insert date. Right here, we have an example of the update function. We have the create or replace function. And we are using the convention fn underscore employee underscore cambium with the parameter list. And what we have is the key field excluded from the set list, which is incorporated at the word clause. And we are including the update user and the update date. Okay, let's continue with the next use case. And this use case is the delete. The key fields are used in the word clause to restrict the delete operation. And we finally have a definition of the create view sentence. This create view supports the select list of fields from a table and associated ones using the foreign key constraints as the red function. And the select clouds include the columns of the associated fields. Let's see an example of both cases. In the case of the delete function, we have the create or replace function using the convention fn underscore employee underscore Baja. And as a parameter, we are passing the tool passes the pair of value. Of corresponding to the MPiD primary key field. Right here we have descendants is delete from the table where we are using the key values to eliminate only the record crew belongs to that key. If the operation goes well, it returns to zero. If there is a problem, it will return minus one. And at this section, we have an example of the create or replace view using the syntax B underscore and the table name. Right here we have the other fields of the original table and associated tables using also the name of the table as a prefix of every field to avoid name duplication at the select list. And also we have the definition of the table relations using the left join table on field relationships. If we see the two generation as a process, we will have three steps or stages. The first step is to identify the schema tables to process. The second stage is to execute the tool in Pulsar SQL and the third step will be to obtain the correct functions and view the script so we can execute it to obtain those use cases. Let's review step number one. In the step number one, we have to identify the tables to process. The tool uses the table public take your tabla. Take your tablas is one of the elements that are installed when we execute the installation script. This table, which is named take your underscore tablas includes the schema name, the table name includes a field whole processor. If this field has a value of one, the tool will generate the script. If it does zero, it will not generate the script. To select which table to process and then we have to use this field including one to generate the script or zero to tell the tool not to generate. And we have a field which is called Gion. In this field, this field is the text field and it will be included the script of the crowd functions and the create view statement. Also, this table has four fields which are optionally who were not required but which can be used to identify the file name to store the user who inserts. There's also another field to store the date time of insert. For instance, the field name to store the user who inserts. It could be insert underscore user or created by. In the field name to store the date time of insertion it could be insert underscore date. The field name to store the user who updates. It could be update underscore user and for the file name to store the date time of the update it would be update underscore date. It's important to notice that for the tool to generate the crowd functions requires that the table to process to have a primary key. If it does not have a primary key, the tool will send a message saying that this is not possible to generate the crowd functions because the primary key is required. So this is the first step to identify the tables. We can use a user statement with this syntax to include the tables from different schemas. In the installation script, this table is populated with the table and the schemas of the public one. So if you have all the tables in that schema you don't have to worry about including the tables to process in technical tablet. If you have another schema with tables you can use a statement like this one to do the insertion in the sample fields, sample files of the save file. There is one script to do this job. Step two, in the second step we have to execute the function take you on the score lot. The function take you on the score lot scans the table, take your tablets and for every table mark to process it invokes the function take you on the score generate on the score function with the arguments schema and tablet which produces the code functions and create new statement. All you have to do is in PJ admin for instance you hit select space public, take your lock and what we and this function will produce the functions for the tables mark to process in the take your tablets. We will review all these steps in the demo point of the agenda. And in the first step we obtain the code of the cross functions and create new what we have to do is to select the take your tablets table from the public schema in order to obtain that code. Right here we are selecting schema, table, process and again and we will obtain a data set and in the game field if we double click we will obtain the code to be used by the functions. Okay. This was the theoretical part. If you don't mind we are going to review the demo to illustrate the point. We are going to exchange a monitor will take a minute. We are going to switch our desktop to the PJ admin full in the files you receive. There is one file called but at the user guys you will have to define a connection to the public database. We're assuming that you have that connection. You can do it later. And we are going to enter the public database which is the default one and we will create, we will enter in query mode. In this query mode we are going to load the file name init underscore take your underscore function. We have all the source code in this function and what it will do is it will install three elements that we were, can I move this? Okay. We have the function init underscore take your underscore function. This script will install three elements in the public schema. Okay. Once the script is run if we look at the public schema we are going to have a table name take your tablets and two functions take your lotte and take your genera function. Right here we have the code of take your lotte and right here we will have the code of take your genera function. And in take your tablets we will have the structure of the tape. So this is the first tape. The first step is to create, is to install the function by the script we saw. This was the first script but we have to execute to install the tool. According to user guide the second file two will be demo underscore create tables. With this script we are going to create a schema called demo and we will create the tables we were reviewing at the presentation. That is the departments table, the payment table, the departments table and the employees table. And they will be created in the schema demo. We execute the script, we refresh the schemas. In the schema sections we review another schema called demo, the demo has three tables. Departments, employee and payments. Okay. Once we have the two tables what we need to do is to include these tables in the table take your tablets to tell the tool to use those records for clutch generation. So we are going to load the second demo file which is called identify tables insert. What we're going to do is to include in take your tablets the names of the tables included in this schema. One of the pages. And then we have to insert with this query we insert the table names of the schema table in the public one. So if we use the first script, what we are going to do is to select these fields the schema, tabla, processar, guion, nombre campo, value alta, nombre campo fecha alta, et cetera from the public tables. The previous script what it did it was to insert in this table the name of the schema, the table name. And we put the flag in activation mode. We have and we have the fields that could be used to insert to store the user who inserts the data of insertion, the update date and so forth. Next we will execute once we have identified the tables to process we are going to load a script which is called 05 cloud code. To generate the code also this is not the query the query is function accept to execute the function. We will have to select a function called take your load. Once we execute this function take your load we will receive a result with the following message. I want the message and the message says the process tables where take your tablets, departments, employee, payment and it displays a message that says that if you want to obtain the photos and functions you will have to select the fields from take your tablets. We have prepared a query for that which is query number five and the query number five it will present the following fields. Right here we have a record for the demo, department table and in the column here we will have the code. We will copy the code we're going to use the query for the basic code and this is the code of the tool. We have the create or replace function with the name of the user, the create user the create use case with the parameters. We have the statement in the next section we have the update function and third place we have the functions to delete. Then we have the functions through the read function variety one we have the right here we have the columns definition right here we have the two methods to obtain to get the fields the number of records using the explain method and commented the options to use the pg underscore class to get the same number, the same estimates right here we have the fill names and at the end we have the view. Create statement view, okay. We execute it, we're going to erase this we're going to go to the pavement table we have the same code, the functions the use cases and the creating we're going to recover the code for the growth function for the employee table. This function for the employee table has the insertion of fields except the MPID field because this is a serial type it includes the insert user update user and corresponding to the update to the insert user insert date so there is a current timestamp and in the parameter list it's not included the insert date because the tool assigns the value of the current timestamp so the developer doesn't need to include it in the parameter list. If everything goes well, it will return a zero in other words, if a problem of course it will return negative one. Right here we have the case function for the update there is a list of the fields in the set list there is not included the MPID which is the primary key this is the function for the list right here we have the function for read these are the parameters that we will receive the limit noses in the limit it will include the page size and in the offset it will include the starting point of the page in the returns table the tool includes the fields name of the table and also incorporates the fields of the payment table and to avoid name duplication the tool adds the table name as a prefix of the fields the same goes to the departments right here we have also the methods for obtaining the total of records using the explained command and commented there is the method for using the another way to obtain the total of records we are going to comment we're going to use in this case the table PG class to get the number of records if you would like to prefer to use that method instead of the explain you can go to the source code and comment this line and comment the two previous lines okay in the select statement we also have the fields from the table and the fields of the related tables using the table name as a prefix right here we have the field and right here we have the name of the tape the field name and in the alias in the alias we are using the name of the table and the name of the field so we could differentiate the pay level from the payment level from the pay level of the employee table okay right here we have the use case of the read function using the word clause right here we have it and finally we have the create statement for the view using the same features of the read function okay once we execute the function if we go to the demo schema and rephrase it in the function sections we will have all the functions created so once the functions are created the right thing to do is it will be to test the functions in the zip file you receive there is a file name demo 06 code testing if you open this file you will have an example of the functions with sample values so you could test if the function is working right what we did for to generate this file was to select the function go to the script section select script and in the select script appears the syntax to execute this function what we did was to copy this code for every function let me close this okay so if we would like to to test this function all we could do is select this field execute this segment of the code in return zero if return zero there was no problem then this was the insert you create case next we have an example of the delete function we are doing is to delete a record with the seven as the key value there was no problem with that next appears the function was no problem right here we could evaluate the function by pages right here we have it right here we could use the starting point as three sorry I didn't lock sentence okay we are running out of time so we're going to accelerate this part right here we are using the the select with key payments we're going to insert this field we're going to delete this one right here we will update this record right here we will retry only one record using the composite key with the employee and that the employee stable right here we have this inserts then we'll delete this record and right here we're going to update the record we are going to use this real case using pagination we will obtain the first records the first six records all the first all the fields appears in this in this in this table sometimes when we use the method for 10 records at first at the first time the records are not the total number of records are not updated so to avoid to correct that problem what we'll have to do is only the first time is use the analyze option, analyzer rules and the name of the table and if we hit the statement again we'll have the correct number of records and if we keep continuing insert the records regularly this total of records will be corrected the record is updated so if you regularly if you regularly apply the analyze option to the tables it will keep updated the total number of records okay with this we could paginate the select and finally with this function we could retrieve only the record who has the key value of one okay or we'll cover the use cases of the tool by the demo by the demonstration with this we finish our talk in this last slide we would like to thank our sponsor for the first community in the first place we have our deputy director of IT in the organization her name is Vendafir thank you very much for your support and we also have Marcos Aurelia he is the corporate director of administration and services also we would like to thank our support from the personal staff starting with Maribel Jacoby, Martín Moreno, Alejandro Gallardo, Eduardo González Rocío Perez, Enrique Chavez, Francisco Toledo, Jesús Caudillo, Miguel González Ramiro Gómez, Luis Mateos, Gabriela Merina, Coactemo Renoso, Hugo Linares Y Javier Sosebo our deepest gratitude for all these sponsors and managers for giving us the opportunity to work with first solutions thank you so much amazing that was so thorough thank you so much and to all of our attendees thanks so much for hanging on the line with us and I think that's a testament to how solid of a presentation this is so for our attendees thanks for spending a little bit of your day with us to our speakers thank you so much it's clear that you put a lot of time and thought into this project and this presentation and I look forward to hopefully working with you again on future events cheers