Postgresql Named Parameters

Postgresql Named Parameters

The PostgreSQL control system forces functions to have named parameters using either positional notation or named notation. We use named notation mainly when we need to pass a large number of parameters; using this, the relationship between function arguments and parameters becomes more explicit.

Although we use functions or procedures in PostgreSQL, this allows us to pass data from a function call to procedures, and at the same time can receive data back from functions. This particular action or function is performed only through parameters. As for the discussion of named parameters, we need to declare functions. So now we will see how the functions work with PostgreSQL.

Functions

Postgresql functions are also considered procedures. They are used to perform operations that can usually take several requests if without using functions we want to execute some kind of logic. But by creating functions, we can execute any scenario in a single procedure; only a function call is required to start the operation.

Syntax

CREATE [OR REPLACE] FUNCTION name_of_function (arguments)

RETURNS data type AS $name_of_variable$

DECLARE

Any_declaration;

BEGIN

< body_of_function >

[]

RETURN { name_of_variable | value }

END;

LANGUAGE plpgsql;

Description :

  • First, we mention the function name after using the word “create a function”.
  • The replace function keyword is used when you need to replace an already existing function.
  • Then the function parameters are written in parentheses.
  • At the end we write about the language used.

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

  • Named Parameter
  • Positional parameter

Named Parameter

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

Positional Parameters

If we directly use the values ​​of positional parameters 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 necessary to specify the order.

Implementation

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

CREATE FUNCTION l_u_case(a textu 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 a case using a third variable. If there is a third variable that is uppercase boolean, then concatenate both strings present in variables “a” and “b”. Each line is concatenated with a space between them. In the second option, if no boolean variable is specified, lower case is applied by the same concatenation method. When executed, the following message is displayed.

The select command is used here to select a case with

We will now apply both the named parameter as both the named notation and the positional notation.

Positional notation

In this example, the function call is made with directly written values ​​without using any variable as an argument to the function call. A function call is made using the SELECT statement. The function name is written along with the parameters.

>> SELECT l_u_case(‘Linux’, ‘Hint’, true);

when executed, the capital letters of both strings are concatenated

When executed, the capital letters of both strings are concatenated; this is because we mentioned uppercase letters as the third parameter.

Whereas if you remove the third parameter then the resulting value will be in lowercase.

>> select l_u_case(‘Linux’, ‘Hint’);

named notation

Here values ​​are not directly assigned to the variables in the parameter, but the variables we use in the functions are also used in the function call. This will assign values ​​to all variables. The boolean value ‘u’ is also set to true.

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

Moreover, the operator given here is not an equals sign. Using a simple equals sign or any other sign instead of this operator produces a syntax error.

When you run the above command, you will see that the same string is displayed in capital letters. Similarly, by removing the named notation for uppercase, lowercase strings will be displayed by default.

Besides positional and named notation, there is a third form, “mixed” notation, which contains both named and positional notation. Let’s look at the same example as above. We will directly refer to the strings in the parameter when calling the function, while the boolean variable ‘u’ will be assigned a value via the named parameter.

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

As a result, there are no changes.

Example

Here is another example of a table named sample; we use the “create” operator to create a function. This function will replace two values. Both variables are introduced into the function. Values ​​within these variables sent via a function parameter will be swapped.

select x, y into y,x;

Run the command and you will see that the function has been created. Now we will make a simple function call using 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 can be confusing, since one might not know the x value and the y value at the time of the function call. So this issue is solved by name parameters. Each value is assigned to a variable as a parameter during the function call.

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

When a function call is made, both values ​​are assigned. And when executed, each value is swapped.

Conclusion

“Postgres named procedure” describes the function, its use with parameters, and the types of parameters that PostgreSQL allows the user to use. There are two main designations for parameters. Both are illustrated with examples so that the user can easily distinguish between these designations.

Postgresql Named Parameters

Leave a Reply

Your email address will not be published.

Scroll to top