PostgreSQL custom function and call method

1. Background

In PostgreSQL relational databases, we often call the system's default functions, such as lower(), array_to_string(), etc., but sometimes with special requirements, the default function cannot implement conversion, so we need to use custom functions and call our customized functions to implement data conversion.

2. Function syntax

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END;  $variable_name$ LANGUAGE plpgsql;

illustrate

function_name: Specify the name of the function.
[OR REPLACE]: is optional, it allows you to modify/replace existing functions.
arguments:Function parameter[[argmode]argname argtype [default value],[[argmode]argname argtype],[…]];The modes of the argmode parameter include IN, OUT or INOUT, and the default value is IN. argname parameter name. The data type of the argtype parameter. default default parameter.
RETURNS: It specifies the type of data to be returned from the function. It can be a basic, compound or domain type, or it can also refer to the types of table columns, such as return int, varchar. When returning the result set, you need to represent it. Void is used without a return value. If an OUT or INOUT parameter exists, the RETURNS clause can be omitted.
return_datatype: Indicates the return value type.
function_body: function_body contains executable parts.
LANGUAGE: It specifies the name of the language that implements the function.

3. Exception handling syntax

Can be utilized in PostgreSQLRAISEStatements report information and throw errors, and their declaration form is:

RAISE LEVEL 'format' [,expression,[...]]

illustrate:
LEVEL: The levels included include DEBUG (write information to the server log), LOG (write information to the server log, with higher priority), INFO, NOTICE and WARNING (write information to the server log and forward it to the client application, with gradually increasing priority), and EXCEPTION throw an error (usually exiting the current transaction). Whether information of a certain priority level is reported to the client or written to the server log, or both, is controlled by the system initialization parameters of log_min_messages and client_min_messages.

4. Custom function examples

1. format function

The FORMAT() function format used to set parameters according to the format string:

FORMAT(format_string [, format_arg [, ...] ])

Example:

SELECT FORMAT('Hello, %s', 'world!!');

Execution result: Hello, world!!

2. Use default parameter

create or replace function ftest(in fname VARCHAR default 'programming language', cname VARCHAR DEFAULT 'java')
RETURNS VARCHAR as $$
begin
	 return format('%s_%s',fname,cname);
end; $$ LANGUAGE plpgsql;

Call method:

select ftest('language');  --language_java
select ftest(cname=&gt;'java'); --编程language_java

3. Return the quotient of the two numbers according to the input number. If the divisor is 0, a custom exception will be thrown.

CREATE 
	OR REPLACE FUNCTION testdivision ( js1 INT, js2 INT ) RETURNS INT AS $$ DECLARE
	v_re INT;
BEGIN
		raise notice'% divided by %',
		js1,
		js2;
	IF
		js2 = 0 THEN
			raise EXCEPTION 'Can't be separated by 0';
		ELSE v_re := js1 / js2;
		RETURN v_re;
	END IF;
	EXCEPTION --Catch exceptions
	WHEN OTHERS THEN
		RETURN 0;
END;
$$ LANGUAGE plpgsql;

Call method:

select testdivision(8,4);--2
select testdivision(3,0);--0

4. When multiple OUT or INOUT parameters are returned, it is a tuple; each member of the tuple can be returned by placing the function call in the FROM clause.

create or replace FUNCTION test1(in js1 int,inout res1 int,out res2 int) as
$$
begin
   res1:=js1*2;
	 res2:=res1*3;
end;
$$
LANGUAGE plpgsql;

Call method:

select test1(3,2); --test1
                   --(6,18)
select * from test1(3,2);--res1|res2
                         --   6|  18

5. Dynamic execution statement

1. Data preparation

create table tmp(
 bsm VARCHAR(100),
 name VARCHAR(100),
 num int
);
insert into tmp(bsm,name,num) VALUES('a','apple',21);
insert into tmp(bsm,name,num) VALUES('b','banana',11);

2. Example: Dynamically query the number of records in a table

create or replace function getsum(in talename VARCHAR)
RETURNS int as $$
DECLARE
   stmt VARCHAR;
	 count int;
begin
  stmt:=format('select count(1) from %s', talename);
  raise notice '%',stmt;
	EXECUTE stmt into count;
	return count;
	EXCEPTION --Catch exceptions
	WHEN OTHERS THEN
		RETURN 0;
end; $$ LANGUAGE plpgsql;

Call method:

select getsum('tmp');--2

3. Dynamically create tables

create or replace function copytable(tablename varchar,times int)
RETURNS INT as $$
DECLARE
   stmt VARCHAR='create table %s_%s (like tmp including all);';--
begin
   for i in 1..times loop
	     raise notice 'dCurrent times%',i;
			 raise notice '%',format(stmt,tablename,i,tablename);
			 EXECUTE format('drop table if EXISTS %s_%s;',tablename,i) ;
			 EXECUTE format(stmt,tablename,i,tablename) ;
	 end loop;
	 return 0;			
END; $$  LANGUAGE plpgsql;

Call method:

select copytable('tmp',2);That is, copytmpsurface2Second-rate

This is the article about how PostgreSQL custom functions and calls. For more information about PostgreSQL custom functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!