PostgreSQL

Postgresql named parameters

PostgreSQL management system makes functions have named parameters by using either position notation or named notation. We use the named notation mostly in the situation where we have to pass a large number of parameters; by using this, the association becomes more explicit between the arguments and the parameters of functions.

While we are using functions or procedures in PostgreSQL, it allows us to pass data from the function call to the procedures and also, at the same time, can receive data back from the functions. This specific action or functionality is only done through the parameters. As for the discussion of the named parameters, we have to declare functions. So we will now see how functions work with PostgreSQL.

Functions

Postgresql functions are also considered as the procedures. These are used to perform operations that usually can take several queries if, without using functions, we want to perform any logic. But by creating functions, we can perform any scenario in a single procedure; only a function call is needed to start the operation.

Syntax

CREATE [OR REPLACE] FUNCTION name_of_function (arguments)

RETURNS datatype AS $name_of_variable$

DECLARE

Any_declarartion;

BEGIN

< body_of_function >

[...]

RETURN { name_of_variable | value }

END;

LANGUAGE plpgsql;

Description:

  • First, we mention the name of the function after using the word ‘create function’.
  • A keyword, replace function, is used if you need to replace an already existing function.
  • Then function parameters are written inside the parenthesis.
  • In the end, we write about the language used.

There are two types of parameters that are passed through the function.

  • Named parameter
  • Positional parameter

Named parameter

If we specify the parameters of a function by using a named notation, then this is a named parameter, and an arrow with the parameter value follows it.

Positional parameters

If we directly use the positional parameters’ values as a positional notation, then the parameters must be listed in the same order in which they are declared. Whereas in the case of named notation, it is not mandatory to specify an order.

Implementation

To elaborate the concept of a named parameter, we will first create a function or a procedure in PostgreSQL. A function named ‘l_u_case’ is created to work on the strings sent to it through a function call. It converts the words into upper or lower case. By creating the function, the main feature is its parameters that contain variables for data receiving. So in this function, we have created two text-type variables to store data in it. And the third variable is a Boolean type. It is well known that the Boolean flag variable value is either true or false. So a variable of uppercase is set as false by default. This means that if the user uses a function call without mentioning the case name, either upper or lower, then by default, the text is converted into the lowercase, if it is in the upper case letters as well.

CREATE FUNCTION l_u_case(a text, u Boolean DEFAULT false)

RETURNS text

AS

$$

   SELECT CASE

          WHEN $3 THEN UPPER ($1 || ' ' || $2)

          ELSE LOWER ($1 || ' ' || $2)

          END;

$$

LANGUAGE SQL IMMUTABLE STRICT;

The select command is used here to select the case by using the third variable. If the third variable that is Boolean for uppercase is present, then concatenate both the strings present in the “a” and “b” variables. Each string is concatenated with a blank space between them. In the second option, if the Boolean variable is not mentioned, the lower case is applied with the same concatenation method. On execution, the following message is displayed.

Now we will apply both the named parameter as a named notation and a positional notation.

Positional notation

In this example, the function call is made with the values directly written without using any variable as an argument for the function call. The function call is accomplished by using a SELECT statement. The name of a function is written with the parameters.

>> SELECT l_u_case('Linux', 'Hint', true);

On execution, an uppercase of both the strings is concatenated; this is because we have mentioned the uppercase as a third parameter.

Whereas, if you remove the third parameter, then the resultant value will be in the lower case alphabets.

>> select l_u_case(' Linux', 'Hint');

Named notation

Here, the values are not directly assigned to the variables in the parameter, but those variables we use in the functions are also used in the function call. By doing this, all the variables will be allotted the values. A Boolean value ‘u’ is also assigned with a true value.

>> SELECT l_u_case( a => 'Linux', b => 'Hint' , u => true);

Moreover, the operator that issued here is not an equal sign. If you use a simple equal sign or any other sign in the place of this operator, a syntax error is formed.

On the execution of the above command, you will see that the same string is displayed with the upper case letters. Similarly, by removing the named notation for the uppercase, the strings in lower case will be displayed by default.

Besides the position and the named notation, there is a third form, a ‘mixed’ notation that contains both the named and the position notation. Let us consider the same example for that as described above. We will directly mention the strings in the parameter in the function call, whereas the boolean variable ‘u’ will be assigned the value through a named parameter.

>> select l_u_case('Linux', 'Hint', u => true);

There is no change in the result.

Example 2

Here is another example of a table named sample; we use a ‘create’ statement to create a function. Two values will be swapped by using this function. Both variables are introduced in the function. The values inside these variables, sent through the function parameter, will be swapped.

Select x, y into y, x;

Execute the command, and you will see that function is created. Now we will perform a simple function call by using a positional notation. We will only use the numbers accepted by the variables declared in the function.

>> select * from sample (531, 902);

On execution, you can see that the values of both variables are swapped. But this might confuse, as someone may don’t know the value of x and the value of y at the time of the function call. So this issue is resolved by the name parameters. Each value is assigned to a variable as a parameter at a time of a function call.

>> select * from swapper (x => 198, y => 457);

While performing the function call, both values are assigned. And on execution, each value is swapped.

Conclusion

‘Postgres named procedure’ describes the function, its use with the parameters, and also the parameter types that PostgreSQL allows the user to use. There are two basic notations of parameters. Both are explained with examples so that the user can easily distinguish between both these notations.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.