DBA Master

A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient. First, declare a cursor. Next, open the cursor. Then, fetch rows from the result set into a target. After that, check if there is more row left to fetch. If yes, go to step 3, otherwise, go to step 5. Finally, close the cursor. he following is valid for the cursor: NEXT LAST PRIOR FIRST ABSOLUTE count RELATIVE count FORWARD BACKWARD Using DECLARE CURSOR and FETCH test=# BEGIN; BEGIN test=# DECLARE mycur CURSOR FOR SELECT * FROM t_large WHERE id > 0; DECLARE CURSOR test=# FETCH NEXT FROM mycur; test=# FETCH 4 FROM mycur; test=# COMMIT; COMMIT I...

Creating a Trigger in PostgreSQL PostgreSQL Triggers are database call-back functions, which are automatically performed/invoked when a specified database event occurs. The following are important points about PostgreSQL triggers − PostgreSQL trigger can be specified to fire Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted) After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed) A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies. If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actions will be executed relative to the insertion, modif...

Benefits of Using Stored Procedures: Transaction control allowing us to COMMIT and ROLLBACK inside procedures. Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver. As you can see there are a couple of similarities between CREATE FUNCTION and CREATE PROCEDURE so things should be really easy for most end users. In this syntax: First, specify the name of the stored procedure after the CREATE PROCEDURE clause. Next, define a parameter list which is similar to the parameter list of user-defined functions. Then, specify the programming language for the stored procedure such as PLpgSQL and SQL. After that, place the code in the body of the stored procedure after that AS keyword. Finally, use double dollar ($$) to end the stored procedure. CREATE TABLE accounts ( id INT GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(100) NOT NULL, balance DEC(15,2) NOT NULL, PRIMARY KEY(id) ); INSERT INTO accounts(name,balance) V...

PostgreSQL allows more than one function to have the same name, so long as the arguments are different. If more than one function has the same name, we say those functions are overloaded. When a function is called, PostgreSQL determines the exact function is being called based on the input arguments. Let’s take a look at the following get_rental_duration() function. Input: CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER) RETURNS INTEGER AS $$ DECLARE rental_duration INTEGER; BEGIN SELECT INTO rental_duration SUM( EXTRACT( DAY FROM return_date - rental_date)) FROM rental WHERE customer_id=p_customer_id; RETURN rental_duration; END; $$ LANGUAGE plpgsql; Output: SELECT get_rental_duration(232); Input: CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER, p_from_date DATE) RETURNS INTEGER AS $$ DECLARE rental_duration integer; BEGIN SELECT INTO rental_duration SUM( EXTRACT( DAY FROM return_date + '12:00:00' - rental_date...

PostgreSQL CREATE FUNCTION Statement: Syntax: CREATE FUNCTION function_name(p1 type, p2 type) RETURNS type AS BEGIN -- logic END; LANGUAGE language_name; Must follow: First, specify the name of the function aftr the CREATE FUNCTION keywords. Then, put a comma-separated list of parameters inside the parentheses following the function name. Next, specify the return type of the function after the RETURNS keyword. After that, place the code inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword. Finally, indicate the procedural language of the function e.g., plpgsql in case PL/pgSQL is Example: CREATE FUNCTION inc(val integer) RETURNS integer AS $$ BEGIN RETURN val + 1; END; $$ LANGUAGE PLPGSQL; PL/pgSQL Function Parameters: IN parameters OUT parameters INOUT parameters VARIADIC parameters PL/pgSQL IN parameters: CREATE OR REPLACE FUNCTION get_sum( a NUMERIC, b NUMERIC) RETURNS NUMERIC AS $$ BEGIN R...

PL/pgSQL CASE Statement: Syntax: CASE search-expression WHEN expression_1 [, expression_2, …] THEN when-statements [ … ] [ELSE else-statements ] END CASE; Example: SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test; PL/pgSQL Loop Statements: LOOP WHILE loop FOR loop PL/pgSQL Loop: The LOOP statement is also referred to as an unconditional loop statement because it executes the statements until the condition in the EXIT statement evaluates to true. Note that the condition specified after the WHEN keyword in the EXIT statement is a Boolean expression that evaluates to true or false. Loop statements can be nested. A LOOP statement is placed inside another LOOP statement is known as a nested loop. In this case, you need to the loop label to specify explicitly which loop you want to terminate in the EXIT statement. Syntax: <> LOOP Statements; EXIT [<>] WHEN condition; END LOOP; Exam...