![]() Second, enter the above code in the psql to create the function like this: dvdrental=# create function get_film_count(len_from int, len_to int)ĭvdrental-# returns int dvdrental-# language plpgsql dvdrental-# as dvdrental-# $$ dvdrental$ # declare dvdrental$ # film_count integer dvdrental$ # begin dvdrental$ # select count(*) dvdrental$ # into film_count dvdrental$ # from film dvdrental$ # where length between len_from and len_to dvdrental$ # dvdrental$ # return film_count dvdrental$ # end dvdrental$ # $$ Code language: Shell Session ( shell ) 2) Creating a function using psqlįirst, launch the psql interactive tool and connect to the dvdrental database. In case you could not find the function name, you can right-click the Functions node and select Refresh… menu item to refresh the function list. ![]() It means that the function get_film_count is created successfully.įinnally, you can find the function get_film_count in the Functions list: If everything is fine, you will see the following message: CREATE FUNCTION ![]() Third, enter the above code int the query tool and click the Execute button to create the get_film_count function. Second, open the query tool by selecting Tools > Query Tool. To execute the create function statement, you can use any PostgreSQL client tool including psql and pgAdmin 1) Creating a function using pgAdminįirst, launch the pgAdmin tool and connect to the dvdrental sample database. At the end of the block, use the return statement to return the film_count. In the body of the block, use the select into statement to select the number of films whose length are between len_from and len_to and assign the result to the film_count variable.In the declaration section, declare a variable called film_count that stores the number of films selected from the film table.Between these $$, you can place a block that contains the declaration and logic of the function. Use the dollar-quoted string constant syntax that starts with $$ and ends with $$.Finally, the language of the function is plpgsql indicated by the language plpgsql.Third, the get_film_count function returns an integer specified by the returns int clause.Second, the get_film_count() function accepts two parameters len_from and len_to with the integer datatype.First, the name of the function is get_film_count that follows the create function keywords.The function get_film_count has two main sections: header and body. ![]() $$ Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Where length between len_from and len_to The following statement creates a function that counts the films whose length between the len_from and len_to parameters: create function get_film_count(len_from int, len_to int) We’ll use the film table from the dvdrental sample database. PostgreSQL Create Function statement examples
0 Comments
Leave a Reply. |