postgres stored procedure pl pgSQL - ghdrako/doc

Linter - static anasises

CREATE EXTENSION plpgsql;
\dx
select * from pg_extension where extname='plpgsql';

PostgreSQL allows user-defined functions to be written in other languages besides SQL and C. These other languages are generically called procedural languages (PLs). There are currently four procedural languages available in the standard PostgreSQL distribution: PL/pgSQL (Chapter 43), PL/Tcl (Chapter 44), PL/Perl (Chapter 45), and PL/Python (Chapter 46). There are additional procedural languages available that are not included in the core distribution.

For the languages supplied with the standard distribution, it is only necessary to execute CREATE EXTENSION language_name to install the language into the current database.

Compare

language sql	language pl/pgsql
For simple scalar queries.	dynamic SQL, where you build and EXECUTE statements dynamically.
can only use pure SQL	has loops, variables, error/exception handling, etc. Not all SQL is valid PL/PgSQL - as you discovered, for example, you can't use SELECT without INTO or RETURN QUERY. PL/PgSQL may also be used in DO blocks for one-shot procedures.
may be inlined
single (or very few) calls per session.	called repeatedly - query plans can be cached for all SQL statements inside pl/pgsql function - they are treated like prepared statements, the plan is cached for repeated calls within the same session
For trigger functions.
ll statements in SQL functions are parsed at once	functions plan and execute each statement sequentially (like a prepared statement).

Compare function procedure

Aspect	Function	Procedure
Deﬁnition and Purpose	designed to take inputs, perform operations, and return a value, which can be used directly in SQL queries	set of SQL statements that perform a speciﬁc task but do not necessarily need to return a value
Syntax and Declaration	`CREATE FUNCTION`	`CREATE PROCEDURE`
Return Type	do not return a value per se	must specify a return type (e.g., INT, TEXT, NUMERIC), and they explicitly return a value using the
RETURN statement.
Invoking	invoked using the CALL statement	invoked as part of a SQL expression
Transaction Control	execute within the context of a single transaction and do not support transaction control commands directly within their body, making them suitable for atomic operations.	can manage transactions using BEGIN, COMMIT, and ROLLBACK statements within their body, enabling ﬁner control over transaction management

PL/SQL supports the following:

Variable declarations
Expressions
Control structures as conditional structures or loop structures
Cursors

We can define the parameters as follows:

IN: Input parameters (if omitted, this is the default option)
OUT: Output parameters
INOUT: Input/output parameters

Functions

Function volatility categories:

VOLATILE - can do everything, including modifying the database. It can return different results on successive calls with the same arguments. A query using a volatile function will re-evaluate the function at every row where its value is needed. Example now() return current date and time
STABLE - cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. It is safe to use an expression containing such a function in an index scan condition. Example lower(string_expression)
IMMUTABLE - cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments.

create or replace function f1(istr  varchar)
 returns text as $func$ 
 select 'hello! '::varchar || istr;
 $func$ language sql;

SELECT * FROM f1('ala');

create or replace function f_multiply_by_5(inout p_inout int) 
as $$
begin
 select p_inout * 5
 into p_inout;
end;
$$ language plpgsql;

select f_multiply_by_5(3);

CREATE FUNCTION f2(istr varchar)
  RETURNS text AS
$func$
BEGIN
   RETURN 'hello! ';  -- defaults to type text anyway
END
$func$ LANGUAGE plpgsql;

create or replace function f_multiply_together(variadic p_nums int[])
 returns int as $$
declare
 v_tot int = 1;
 v_ind int;
begin
 foreach v_ind in array p_nums loop
 v_tot = v_tot * v_ind;
 end loop;
 return v_tot;
end;
$$ language plpgsql;

select f_multiply_together(3,5,2);
select f_multiply_together(12,4,9,8,1,333,1,87,5,2);

CREATE OR REPLACE FUNCTION investment_calculator(
    IN v_amount numeric, IN v_interest numeric,
    IN v_years int)
RETURNS numeric AS
$$
  DECLARE
    v_sum   ALIAS FOR $1;
    v_result  numeric := 0;
  BEGIN
    v_result := v_amount
     * pow(1 + v_interest, v_years);
    RETURN v_result;
  END;
$$ LANGUAGE 'plpgsql';
SELECT investment_calculator(1000, 0.1, 2);

We can name those parameters passed to a function and use them directly instead of using $1, and $2. What is also possible is to use aliases. v_sum can be used just like v_amount, so the effect of an alias and a named parameter is pretty much the same from a coding standpoint.

Variadic unnest

https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
https://www.postgresonline.com/journal/index.php?/archives/406-VARIADIC-Unnest.html unnest function is variadic so can take an unlimited number of arguments

RETURN Type

Return Type	Structure	Usage
returns	Custom record type	returns a single row with a custom structure
returns setOF	Custom record type	returns a set of rows, each of which is a record with a specific structure
returns table	Custom table type	returns a set of rows, each of which has columns defined by the table type Out parameters Custom record type

RETURNS TABLE

CREATE OR REPLACE FUNCTION <function_name> 
RETURNS TABLE (col1 type, col2...)
 AS $$ BEGIN
  RETURN QUERY <your select query>;
END;
$$ LANGUAGE plpgsql;

$$ CREATE OR REPLACE FUNCTION get_users_return_TABLE() 
RETURNS TABLE (id INTEGER, username VARCHAR)
 AS $$ BEGIN
  RETURN QUERY SELECT user_id, name FROM users;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_users_return_TABLE();

Testing

https://pgtap.org/

PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.

functions (a body of function) can be changed 100% safely (without any unavailability). Invalidation of cached function syntax trees works perfect.

Anonymous block

It is not possible to return any value from the unnamed code blocks. If you want to return any value from anonymous blocks, then we might need to use any session-level variables, which need to be set inside the anonymous block, and access them from the outside of the blocks.

nested BEGIN... END block

DO $o$ 
BEGIN  
   RAISE NOTICE $i$ 
      Hello   
      World 
   $i$;
END;
$o$;

DO
$$
BEGIN
  BEGIN
    RAISE NOTICE 'Hello World';
  END;
END;
$$;

nested unnamed code block

DO
$o$
BEGIN
  DO
  $i$
  BEGIN
    RAISE NOTICE 'Hello World';
  END;
  $i$;
END;
$o$;

nested block inside an exception

DO $inline$
BEGIN
  PERFORM 1/0;
  RAISE NOTICE 'Hello World!';
EXCEPTION
  WHEN OTHERS THEN
  DO $$
  BEGIN
    RAISE NOTICE 'Got error';
  END;
  $$;
END;
$inline$;

Named block

<<label>>
DECLARE
-- declare variables here
BEGIN
-- Named block's code here
END;

nested code blocks can refer to outer variables by using that label instead of finding the innermost match for the variable name.

Once a named block has been defined, it can be called from within the same function or procedure using the PERFORM statement:

PERFORM block_name;

Named blocks can be called multiple times within the same function or procedure, allowing for reusable and modular code.

CREATE OR REPLACE FUNCTION example_nested_loops()
RETURNS VOID AS $$
BEGIN
    <<outer_loop>> -- Label for the outer loop
    FOR i IN 1..5 LOOP
        <<inner_loop>> -- Label for the inner loop
        FOR j IN 1..5 LOOP
            IF i * j > 10 THEN
                -- Exit both loops when a condition is met
                EXIT outer_loop;
            END IF;
            -- Your inner loop logic here
        END LOOP inner_loop;
        -- Your outer loop logic here
    END LOOP outer_loop;
END;
$$ LANGUAGE plpgsql;

Variable scope

The scope of the declared variables in PL/pgSQL is always local to its current block.

To access the parent v_var1 variable, we should access that variable with the block’s label: parent.v_var1

$$
<<parent>>
DECLARE
v_var1 INT := 1;
BEGIN
  DECLARE
    v_var1 INT := 10;
  BEGIN
    RAISE NOTICE 'Parent v_var1 %', parent.v_var1;
    RAISE NOTICE 'Local v_var1 %', v_var1;
  END;
END;
$$;

Dollar-Quoted String Constants

-- Escaping single quote inside string
select 'I''m also a string constant';
select E'I\'m also a string constant';  -- in old version postgres

Syntax

$tag$<string_constant>$tag$

select $$I'm a string constant that contains a backslash \$$;
SELECT $message$I'm a string constant that contains a backslash \$message$;

Using dollar-quoted string constant in anonymous blocks

do 
$$
declare
   film_count integer;
begin 
   select count(*) into film_count
   from film;
   raise notice 'The number of films: %', film_count;
end;
$$

Using dollar-quoted string constants in functions

create function find_film_by_id(
   id int
) returns film 
language sql
as 
$$
  select * from film 
  where film_id = id;  
$$;

Using dollar-quoted string constants in stored procedures

create procedure proc_name(param_list)
language lang_name
as $$
  -- stored procedure body
$$

Storage function

CREATE FUNCTION repeat(times integer, s text)
    RETURNS text
    AS $$
DECLARE
    result text;
BEGIN
    result := '';
    FOR i IN 1..times LOOP
        result := result || s;
    END LOOP;
    RETURN result;
END;
$$
LANGUAGE plpgsql
IMMUTABLE;

-- psql> SELECT repeat(10, '*');
--    repeat
-- ------------
--  **********
-- (1 row)

Stored procedures

Compared with stored functions, there is only one extra thing that procedures can do – transaction control.

CREATE PROCEDURE check_commit(v integer)
LANGUAGE plpgsql AS $$
BEGIN
    IF v % 2 = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END $$;

-- call it
CALL check_commit(10);

CREATE PROCEDURE annual_pay_rise (percent numeric)
LANGUAGE plpgsql AS $$
DECLARE
  c CURSOR FOR
  SELECT * FROM employee
   WHERE job_code = 'A2';
BEGIN
  FOR r IN c LOOP
    UPDATE employee
       SET salary = salary * (1 + (percent/100.0))
     WHERE empid = r.empid;
    IF mod (r.empid, 100) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
END;
$$;

CALL annual_pay_rise(2);

Implementation of restart mechanism

CREATE TABLE job_status
(id bigserial not null primary key,status text not null,restartdata bigint);
CREATE OR REPLACE FUNCTION job_start_new ()
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  p_id BIGINT;
BEGIN
  INSERT INTO job_status (status, restartdata)
     VALUES ('START', 0)
   RETURNING id INTO p_id;
  RETURN p_id;  
END; $$;
CREATE OR REPLACE FUNCTION job_get_status (jobid bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
rdata BIGINT;
BEGIN
  SELECT restartdata INTO rdata
    FROM job_status
    WHERE status != 'COMPLETE' AND id = jobid;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'job id does not exist';
  END IF;
  RETURN rdata;
END; $$;
CREATE OR REPLACE PROCEDURE
job_update (jobid bigint, rdata bigint)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE job_status
    SET status = 'IN PROGRESS'
       ,restartdata = rdata
    WHERE id = jobid;
END; $$;
CREATE OR REPLACE PROCEDURE job_complete (jobid bigint)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE job_status SET status = 'COMPLETE'
    WHERE id = jobid;
END; $$;

CREATE OR REPLACE PROCEDURE annual_pay_rise (job bigint)
LANGUAGE plpgsql AS $$
DECLARE
        job_empid bigint;
        c NO SCROLL CURSOR FOR
                SELECT * FROM employee
                WHERE job_code='A2'
                AND empid > job_empid
                ORDER BY empid;
BEGIN
        SELECT job_get_status(job) INTO job_empid;
        FOR r IN c LOOP
                UPDATE employee
                SET salary = salary * 1.02
                WHERE empid = r.empid;
                IF mod (r.empid, 100) = 0 THEN
                        CALL job_update(job, r.empid);
                        COMMIT;
                END IF;
        END LOOP;
        CALL job_complete(job);
END; $$;

Trigger

To create a new trigger in PostgreSQL, you follow these steps:

First, create a trigger function using CREATE FUNCTION statement.

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

Second, bind the trigger function to a table by using CREATE TRIGGER statement.

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

The event can be INSERT , DELETE, UPDATE or TRUNCATE.

Example

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.last_name <> OLD.last_name THEN
		 INSERT INTO employee_audits(employee_id,last_name,changed_on)
		 VALUES(OLD.id,OLD.last_name,now());
	END IF;

	RETURN NEW;
END;
$$

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

Rename trigger

ALTER TRIGGER trigger_name
ON table_name 
RENAME TO new_trigger_name;

\dS <table_name> -- view all triggers associated with a table

PostgreSQL doesn’t support the OR REPLACE statement that allows you to modify the trigger definition like the function that will be executed when the trigger is fired.

In order to do so, you can use the DROP TRIGGER and CREATE TRIGGER statements.

Disable/enable trigger

ALTER TABLE table_name
DISABLE TRIGGER trigger_name | ALL

ALTER TABLE table_name
ENABLE TRIGGER trigger_name | ALL

Iterations

unconditional loop, which is repeated infinitely until an explicit EXIT is invoked. EXIT forces the current loop to stop immediately and can be subject to a Boolean condition specified by a WHEN predicate

testdb=> DO $code$
 DECLARE
   counter int := 0;
 BEGIN
   LOOP
      counter := counter + 1;
      RAISE INFO 'This is the % time I say HELLO!', counter;
      EXIT WHEN counter > 3;
   END LOOP;

   RAISE INFO 'Good bye';
 END $code$;

INFO:  This is the 1 time I say HELLO!
INFO:  This is the 2 time I say...

pg_sleep pg_sleep_for pg_sleep_until

DO $$ BEGIN PERFORM pg_sleep(10); END $$;
SELECT pg_sleep(10);
SELECT pg_sleep_for('10 seconds');
SELECT pg_sleep_until('today 21:45');
SELECT pg_sleep_until('tomorrow 03:00');

\x
SELECT 
  clock_timestamp(),
  pg_sleep(1),
  clock_timestamp(),
  pg_sleep(1),
  clock_timestamp();

SELECT 
  clock_timestamp(),
  pg_sleep(.5),
  clock_timestamp(),
  pg_sleep(.5),
  clock_timestamp();

Views

To prevent users from the insert or update a row that not visible through the view, you use the WITH CHECK OPTION clause when creating the view. ''WITH CASCADED CHECK OPTION'' - checked the view-defining condition of view and also all the underlying views. To check the view-defining condition of the view that you insert or update, you use the WITH LOCAL CHECK OPTION.

Dynamic queries

Using format function to construct statement

The rule of thumb for dynamic SQL: pass values/parameters through $x placeholders with the using clause. Inject identifiers (table names, column names) using %I of the format() function. So your code should be:

execute format('select 1 from schema.%I where id = $1', 'table_'||tablesuffix)
  using id --<< passes the value of the variable id as the value for $1
  into exists;

Dynamic SQL in pl/pgsql is written using the EXECUTE statement. The basic syntax of the EXECUTE statement is as follows:

EXECUTE statement_name [ INTO target ] [ USING expression [, ...] ];

The INTO clause is used to specify the target variable or variables for the result of the query. The USING clause is used to supply values for any parameters used in the SQL statement.

CREATE OR REPLACE FUNCTION test_func_dynamic_sql(input_table_name text)
RETURNS TABLE(id integer, name text)
AS $$
BEGIN
  EXECUTE 'SELECT id, name FROM ' || input_table_name INTO id, name;
  RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_func_dynamic_sql('test_dynamic_sql');

Dynamic table creation

CREATE OR REPLACE FUNCTION create_dynamic_table(table_name text,columns jsonb)
RETURNS void AS $$
DECLARE
  column_rec record;
  column_def text;
  sql_stmt text;
BEGIN
  -- Create column definitions
  FOR column_rec IN SELECT * FROM jsonb_each_text(columns)
  LOOP
    column_def := column_rec.key || ' ' || column_rec.value;
    IF sql_stmt IS NULL THEN sql_stmt := column_def;
    ELSE sql_stmt := sql_stmt || ', ' || column_def;
    END IF;
  END LOOP;
  -- Execute dynamic SQL to create the table
  EXECUTE 'CREATE TABLE ' || table_name || '(' || sql_stmt || ')';
END;
$$ LANGUAGE plpgsql;

SELECT create_dynamic_table('customer_data', '{"name": "text","age": "integer"}');

Dynamic query building

It uses CASE statements to add conditions only if their corresponding input isn’t null.

CREATE OR REPLACE FUNCTION search_records(id int, name text, city text,
state text)
RETURNS TABLE(v_id integer, v_name text, v_city text, v_state text)
AS $$
BEGIN
  EXECUTE 'SELECT id, name, city, state FROM records WHERE TRUE' ||
    CASE WHEN name IS NOT NULL THEN ' AND name = ' || quote_literal(name) ELSE '' END ||
    CASE WHEN city IS NOT NULL THEN ' AND city = ' || quote_literal(city) ELSE '' END ||
    CASE WHEN state IS NOT NULL THEN ' AND state = ' || quote_literal(state) ELSE '' END
  INTO v_id, v_name, v_city, v_state;
  RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM search_records(1, 'foo', 'LA', 'California');

Dynamic index creation

CREATE OR REPLACE FUNCTION create_index(table_name text, column_name text,index_name text)
RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE INDEX ' || index_name || ' ON ' || table_name || '(' || column_name || ')';
END;
$$ LANGUAGE plpgsql;

Dynamic column selection

CREATE OR REPLACE FUNCTION select_columns(table_name text, column_names text[])
RETURNS record AS $$
DECLARE
  rec record;
BEGIN
  EXECUTE 'SELECT ' || array_to_string(column_names, ', ') || ' FROM ' || table_name INTO rec;
  RETURN rec;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM select_columns('employee_data', ARRAY['first_name',
'salary']) as foo(fname text, sal numeric);

Preventing SQL Injection

EXECUTE 'SELECT * FROM employees WHERE first_name = ' || quote_literal(input_name);

CREATE TABLE test_dynamic_sql (id int, name varchar);
INSERT INTO test_dynamic_sql VALUES(1, 'test1');
 – Create a function that uses dynamic SQL:
CREATE OR REPLACE FUNCTION test_func_dynamic_sql(input_table_ name text) RETURNS TABLE(id integer, name text) AS $$ BEGIN  EXECUTE 'SELECT id, name FROM ' || input_table_name INTO id, name;
 RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_func_dynamic_sql('test_dynamic_sql');
 id | name 
----+------- 
1 | test1 
(1 row)

EXECUTE statement constructs an SQL statement by concatenating the input parameter with a string containing the SELECT statement. The INTO clause is used to store the results of the query into the id and name variables.

Dynamic Index Creation

CREATE OR REPLACE FUNCTION create_index(table_name text, column_name text, index_name text) 
RETURNS void 
AS 
$$ 
BEGIN  
  EXECUTE 'CREATE INDEX ' || index_name || ' ON ' || table_name || '(' || column_name || ')';
END;
$$ LANGUAGE plpgsql;

SELECT create_index('sample_table', 'column1', 'sample_column1_index');

Dynamic Column Selection

CREATE OR REPLACE FUNCTION select_columns(table_name text, column_ names text[]) 
RETURNS record 
AS 
$$ 
DECLARE
  rec record;
BEGIN
  EXECUTE 'SELECT ' || array_to_string(column_names, ', ') || ' FROM ' || table_name INTO rec;
 RETURN rec;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM select_columns('employee_data', ARRAY['first_name', 'salary']) as foo(fname text, sal numeric);

sql injection preventing

Parameterized Queries: Use parameterized queries or prepared statements to separate data from SQL code. This approach ensures that user inputs are treated as values rather than executable code.
Quoting and Escaping: When constructing dynamic SQL, use proper quoting and escaping functions to handle user inputs securely. PostgreSQL provides functions like quote_literal and quote_ident to escape and quote input values. Also, quote_ nullable is useful when input values are null.

EXECUTE 'SELECT * FROM employees WHERE first_name = ' || quote_ literal(input_name);

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING my_age, my_id;
END;                                              
$$ LANGUAGE plpgsql

Prepared statements

PREPARE dynamic_query(text) AS  SELECT * FROM employees WHERE last_name = $1;
EXECUTE dynamic_query(input_last_name);

PREPARE rental_insert (INTEGER, INTEGER, TIMESTAMP, INTEGER) AS
    INSERT INTO rental (rental_id, inventory_id, rental_date, customer_id)
    VALUES ($1, $2, $3, $4);

EXECUTE rental_insert(10001, 2019, '2023-06-01 09:00:00', 153);

Parametrize statment with using

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

A cleaner approach is to use format()'s %I specification to insert table or column names with automatic quoting:

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

Cursors

PL/pgSQL supports cursors, which are a mechanism for traversing through the records of a result set.

Implicit cursor

FOR LOOP to iterate over the SQL statement, which creates an implicit cursor in PostgreSQL and iterates over the result set.

postgres=# DO 
 $$ 
DECLARE v_rec RECORD;
BEGIN 
  FOR v_rec IN (SELECT * FROM many_rows_table) 
  LOOP 
   ...
  END LOOP;
END;
$$;

Using cursors can help reduce the response time when dealing with large datasets, as it allows the user to fetch a small, manageable number of records at a time. This is particularly useful when rendering data in a dashboard with pagination, where only a certain number of records are displayed at once. Cursors also allow for processing of the data as needed, rather than returning the entire result set at once to the client.

CURSOR Attributes

FOUND Attribute returns true if the last operation on the cursor found a row, and false otherwise.

DO 
$$ 
DECLARE  
  v_cur CURSOR FOR SELECT * FROM many_rows_table;
  v_rec many_rows_table%ROWTYPE;
BEGIN  
  OPEN v_cur;
  FETCH v_cur INTO v_rec;
  IF FOUND THEN
    RAISE NOTICE 'Row found.';
  ELSE
    RAISE NOTICE 'No rows found.';
  END IF;
  CLOSE v_cur;
END;
$$;

To opposit You can use IF NOT FOUND THEN

ROWCOUNT Attribute returns the number of rows processed by the last FETCH or MOVE statement. You can use the GET DIAGNOSTICS command’s ROW_COUNT item as an alternative for this

DO 
$$ 
DECLARE  
  v_cur CURSOR FOR SELECT * FROM many_rows_table;
  v_rec many_rows_table%ROWTYPE;
  num_rows int;
BEGIN   
  OPEN v_cur;
  MOVE FORWARD ALL FROM v_cur;
  GET DIAGNOSTICS num_rows = ROW_COUNT;
  RAISE NOTICE 'Number of rows fetched: %', num_rows;
  CLOSE v_cur;
END;
$$;

Monitor Cursors

Unlike other database engines where cursors have to be explicitly closed, in PL/pgSQL cursors will be automatically closed when the transaction ends or when there is an exception in the current execution block. The implicit transactions will also be closed when their task is done. To monitor the current active transaction in PostgreSQL, we can use the PostgreSQL system catalog table “pg_cursors” , which provides insight into the details of opened cursors:

postgres=# \d pg_cursors
SELECT name, creation_time FROM pg_cursors;

PL/pgSQL provides holdable cursors, as well as scrollable cursors that must be explicitly set when creating external triggers.

SCROLL Cursor

PL/pgSQL provides the option to use scrollable cursors, which allow you to move back and forth through the result set. This is particularly useful for applications that require random access to the results, such as when implementing search functionality. To use a scrollable cursor, you must declare it with the SCROLL keyword and then open it with the OPEN statement. Once the cursor is open, you can fetch rows using the FETCH statement and then move forward or backward through the result set using the MOVE statement. Finally, you must close the cursor using the CLOSE statement.

postgres# BEGIN WORK;
DO 
$$ 
DECLARE 
  v_cur SCROLL CURSOR FOR SELECT * FROM scroll_test;
BEGIN 
  OPEN v_cur;
END;
$$;
postgres# FETCH NEXT FROM v_cur;
postgres# FETCH NEXT FROM v_cur;
postgres# MOVE BACKWARD 1 IN v_cur;
postgres# MOVE BACKWARD 1 IN v_cur;
postgres# FETCH NEXT FROM v_cur;

By using FETCH NEXT or MOVE BACKWARD, we can control the amount of result sets, which we retrieve from the cursor.. If it is not possible to keep the transaction open for pagination, then we should use WITH HOLD cursors. These cursors hold the cursor data pointer even after the transaction is closed.

“SCROLL” cursors should only be used for read-only operations.

NO SCROLL Cursor

With the NO SCROLL cursor, the data pointer cannot be moved backward. This cursor not only traverses the rows but also allows the traversed rows to be updated/ deleted using DML statements like UPDATE/DELETE.

If the SCROLL behavior of the cursor is not defined, PL/pgSQL will determine whether it should be SCROLL or NO SCROLL. If the SQL is executing FOR UPDATE, then the cursor will be set to “NO SCROLL.” If the SQL is read-only, then the cursor will automatically be set to “SCROLL.”

postgres=# BEGIN WORK;
postgres=*# DO 
$$ 
DECLARE 
  v_cur NO SCROLL CURSOR FOR SELECT * FROM scroll_test FOR UPDATE;
BEGIN 
  OPEN v_cur;
END;
$$;
postgres=# FETCH NEXT FROM v_cur;
postgres=# DELETE FROM scroll_test WHERE CURRENT OF v_cur;
postgres=# FETCH NEXT FROM v_cur;
postgres=# END

WITH HOLD Cursors

Option “WITH HOLD” cursors allow the cursor data and pointer to be held even after the transaction is closed. This is useful when dealing with pagination or when you need to preserve the cursor state across multiple transactions.

“WITH HOLD” cursors because they won’t close automatically until we close the session.

postgres=# DO 
$$ 
BEGIN
EXECUTE 'DECLARE cur CURSOR WITH HOLD FOR select * from scroll_test;';
END;
$$;
 postgres=# FETCH NEXT FROM cur;

Currently, PL/pgSQL anonymous blocks do not support the creation of “WITH HOLD” cursors, unlike SCROLL and NO SCROLL cursors. To create these cursors, you must use named functions or procedures. Alternatively, you can create “WITH HOLD” cursors using a dynamic SQL EXECUTE statement approach in annonymous block.

Refcursors

PL/pgSQL supports reference cursors, which allow you to create a cursor and open it and then pass or return the cursor to another function as an input argument. This means that you can easily pass the cursor data pointer between multiple function calls, which improves code reusability and maintainability. These cursors can be created, opened, and passed or returned as input arguments to other functions.

postgres=# CREATE OR REPLACE FUNCTION test_refcursor(rf REFCURSOR) 
RETURNS VOID 
AS 
$$ 
DECLARE v_rec RECORD;
BEGIN 
  LOOP 
    FETCH rf INTO v_rec;
    RAISE NOTICE 'Record %', v_rec;
    -- print only one record and exit the loop EXIT WHEN FOUND;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;


postgres=# DO 
$$  
DECLARE 
  v_cur REFCURSOR;
BEGIN
  OPEN v_cur FOR SELECT * FROM scroll_test;
  PERFORM test_refcursor(v_cur);
END;
$$;

-- A function that builds a part of dynamic SQL - return string
CREATE OR REPLACE FUNCTION age_category_dyn (p_age text)
RETURNS text language plpgsql AS
$body$
BEGIN
  RETURN ($$CASE
    WHEN $$||p_age ||$$ <= 2 THEN 'Infant'
    WHEN $$||p_age ||$$<= 12 THEN 'Child'
    WHEN $$||p_age ||$$< 65 THEN 'Adult'
    ELSE 'Senior'
  END$$);
END; $body$;


CREATE TYPE passenger_age_cat_record AS (
  passenger_id int,
  age_category text
);

CREATE OR REPLACE FUNCTION passenger_age_category_select (p_limit int)
RETURNS setof passenger_age_cat_record
AS
$body$
BEGIN
  RETURN QUERY
  EXECUTE $$SELECT
             passenger_id,
             $$||age_category_dyn('age')||$$ AS age_category
           FROM passenger LIMIT $$ ||p_limit::text
  ;
END;
$body$ LANGUAGE plpgsql;

--Now, we can execute the following statement:
SELECT * FROM passenger_age_category_select (5000000)
This will take about 11 seconds to execute, which is more than a

-- A function that builds dynamic SQL to search by different criteria
CREATE OR REPLACE FUNCTION select_booking_dyn (p_email text,
p_dep_airport text,
p_arr_airport text,
p_dep_date date,
p_flight_id int)
returns setof booking_record_basic
as
$func$
DECLARE
v_sql text:='SELECT DISTINCT b.booking_id, b.booking_ref, booking_name,
account_id, email
FROM booking b ';
v_where_booking text;
v_where_booking_leg text;
v_where_flight text;
BEGIN
IF p_email IS NOT NULL then v_where_booking :=$$ lower(email) like $$
||quote_literal(p_email||'%'); END IF;
IF p_flight_id IS NOT NULL then v_where_booking_leg:= $$ flight_id=$$||p_
flight_id::text;
END IF;
IF p_dep_airport IS NOT NULL
THEN v_where_flight:=concat_ws($$ AND $$, v_where_flight, $$departure_
airport=$$||
quote_literal(p_dep_airport));
END IF;
IF p_arr_airport IS NOT NULL
THEN v_where_flight:=concat_ws($$ AND $$,v_where_flight,
$$arrival_airport=$$||quote_literal(p_arr_airport));
END IF;
IF p_dep_date IS NOT NULL
THEN v_where_flight:=concat_ws($$ AND $$,v_where_flight,
$$scheduled_departure BETWEEN $$||
quote_literal(p_dep_date)||$$::date AND $$||quote_literal(p_dep_
date)||$$::date+1$$);
END IF;
IF v_where_flight IS NOT NULL OR v_where_booking_leg IS NOT NULL
THEN v_sql:=v_sql||$$ JOIN booking_leg bl USING (booking_id) $$;
END IF;
IF v_where_flight IS NOT NULL THEN
v_sql:=v_sql ||$$ JOIN flight f USING (flight_id) $$;
END IF;
v_sql:=v_sql ||$$ WHERE $$||
concat_ws($$ AND $$,v_where_booking, v_where_booking_leg, v_where_flight);
--raise notice 'sql:%', v_sql;
return query EXECUTE (v_sql);
END;
$func$ LANGUAGE plpgsql;

pgcrypto is a set of hashing and encryption functions that allow you to do things like hash a password using blowfish (bcrypt)

CREATE OR REPLACE FUNCTION register(login varchar(50), email varchar(50), password varchar(50), ip inet)
returns TABLE (
  new_id bigint,
  message varchar(255),
  email varchar(255),
  email_validation_token varchar(36)
)
AS
$$
DECLARE
  new_id bigint;
  message varchar(255);
  hashedpw varchar(255);
  validation_token varchar(36);
BEGIN

  --hash the password using pgcrypto
  SELECT crypt(password, gen_salt('bf', 10)) into hashedpw;

  --create a random string for the
  select substring(md5(random()::text),0, 36) into validation_token;

  --create the member. Email has a unique constraint so this will
  --throw. You could wrap this in an IF if you like too
  insert into members(email, created_at, email_validation_token)
  VALUES(email, now(), validation_token) returning id into new_id;

  --set the return message
  select 'Successfully registered' into message;

  --add login bits to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'local',email,hashedpw);

  --add auth token to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'token',null,validation_token);

  -- add them to the members role which is 99
  insert into members_roles(member_id, role_id)
  VALUES(new_id, 99);

  --add log entry
  insert into logs(subject,entry,member_id, ip, created_at)
  values('registration','Added to system, set role to User',new_id, ip, now());

  --return out what happened here with relevant data
  return query
  select new_id, message, new_email, success, validation_token;

END
$$ LANGUAGE plpgsql;

Invalid transaction termination

Nie mozna commit wewnatrz procedury

CREATE OR REPLACE PROCEDURE MyProcedure(lot of args..)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
.....
.....
COMMIT;
END;
$procedure$
;
ERROR: invalid transaction termination

The documentation says: Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There are some other, undocumented, restrictions:

You cannot start a transaction explicitly with BEGIN and commit it inside a transaction. So the following will fail:

    START TRANSACTION;
    CALL procedure_with_commit();

This may be improved in future releases.

All procedures in the call stack must be written in PL/pgSQL:

    CREATE PROCEDURE b() LANGUAGE plpgsql
       AS 'BEGIN PERFORM 42; COMMIT; END;';

    CREATE PROCEDURE a() LANGUAGE sql
       AS 'CALL b()';

    CALL a();
    ERROR:  invalid transaction termination
    CONTEXT:  PL/pgSQL function b() line 1 at COMMIT
    SQL function "a" statement 1

As it is, transaction control inside PostgreSQL procedures is somewhat limited.

If you violate any of these rules, you will get the error message you describe in your question. You will probably have to handle transactions in the application rather than in the procedure — perhaps splitting the procedure into smaller parts makes this possible.

IF statement

IF condition or expression
 THEN  -- Code block to execute when condition is true 
END IF;

PL/pgSQL uses SQL’s SPI interface to perform conditional evaluation. This means that internally it forms a SELECT statement around the given condition, executes it, and returns in the form of boolean. That is, the “1=1” will be converted into the SQL statement “SELECT 1=1” , and the result will be placed in the IF statement.

Statment	conversion
IF (1 = 1) ORDER BY 1 THEN	“SELECT 1=1 ORDER BY 1”
IF (select count(*) from test) < 1 THEN	SELECT (SELECT count(*) FROM test) < 1

Note If conditional statements are placed inside loop statements, there will be a rapid context switch between the pl/pgSQl and SQl engines. this can result in increased resource utilization. If possible, avoid placing conditional expressions inside a huge number of iterations.

IF EXISTS(SELECT * FROM test) 
THEN
  RAISE NOTICE 'table is not empty';
END IF;

Optimal way of checking the table is empty. Dont scan whole table but (stops returning records from SELECT *) the process whenever it finds any single record in the table.

Debuging, instrumenting, logging

https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-get-stacked-diagnostics/

postgres stored procedure pl pgSQL - ghdrako/doc_snipets GitHub Wiki

Linter - static anasises

Compare

Functions

Variadic unnest

RETURN Type

RETURNS TABLE

Testing

Anonymous block

Named block

Variable scope

Dollar-Quoted String Constants

Storage function

Stored procedures

Trigger

Iterations

pg_sleep pg_sleep_for pg_sleep_until

Views

Dynamic queries

Using format function to construct statement

Dynamic table creation

Dynamic query building

Dynamic Index Creation

Dynamic Column Selection

sql injection preventing

Prepared statements

Parametrize statment with using

Cursors

Implicit cursor

CURSOR Attributes

Monitor Cursors

SCROLL Cursor

NO SCROLL Cursor

WITH HOLD Cursors

Refcursors

Invalid transaction termination

IF statement

Debuging, instrumenting, logging

⚠️ GitHub.com Fallback ⚠️

postgres stored procedure pl pgSQL - ghdrako/doc_snipets GitHub Wiki

Linter - static anasises

Compare

Functions

Variadic unnest

RETURN Type

RETURNS TABLE

Testing

Anonymous block

Named block

Variable scope

Dollar-Quoted String Constants

Storage function

Stored procedures

Trigger

Iterations

pg_sleep pg_sleep_for pg_sleep_until

Views

Dynamic queries

Using format function to construct statement

Dynamic table creation

Dynamic query building

Dynamic Index Creation

Dynamic Column Selection

sql injection preventing

Prepared statements

Parametrize statment with using

Cursors

Implicit cursor

CURSOR Attributes

Monitor Cursors

SCROLL Cursor

NO SCROLL Cursor

WITH HOLD Cursors

Refcursors

Invalid transaction termination

IF statement

Debuging, instrumenting, logging

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️