Процедуры и функции Postgre

Отличие функций от процедур

В PostgreSQL функции и хранимые процедуры отличаются по нескольким ключевым аспектам, включая возвращаемое значение, возможность модификации данных и управление транзакциями.

Функция всегда должна возвращать значение, в то время как процедура может возвращать значения через выходные параметры или не возвращать ничего.

Процедуры могут выполнять операции модификации данных (INSERT, UPDATE, DELETE), в то время как фу ции, как правило, предназначены для вычисления и возврата значения, не изменяя данные в таблицах. Кроме того, процедуры могут управлять транзакциями (COMMIT, ROLLBACK), чего функции не поддерживают. 

Примечание: В FSPG для взаимодействия с платформой используются хранимые процедуры с выводом JSON. Функции играют только вспомогательную роль. 

Хранимые процедуры postgreSQL

Процедура в MS SQL:

CREATE PROCEDURE [dbo].[as_forms_getForm]
@code nvarchar(64),
        @username nvarchar(256),
        @langID int = 0
AS
BEGIN
select top 1 * 
from as_forms  f
where code = @code
END

Процедура в Postgre (с учетом использования в FSPG): 

CREATE OR REPLACE PROCEDURE as_forms_getForm(
res out text, code TEXT, username TEXT, langID integer) AS $$
declare 
        formCode text = code;
BEGIN
        CREATE TEMP TABLE sel1 ON COMMIT DROP AS                
        select  f.* from as_forms  f 
    where f.code = formCode;                

        CREATE TEMP TABLE sel2 ON COMMIT DROP AS                
        select  'xx' msg, 1 result;

        select to_jsonb(t1.*) from (
                select                
                (select json_agg(e.*) from sel1 e) sel1,
                (select json_agg(e.*) from sel2 e) sel2        
        ) t1 
        into res;         
        
END;
$$ LANGUAGE plpgsql;

Тестовый вызов такой процедуры: 

DO $$
        DECLARE id INT;
        declare res text;
BEGIN
        CALL as_forms_getForm(res, 'code1', '', 1);
        RAISE NOTICE '%', res;
END $$

Создание хранимки с выходным параметром: 

create or replace procedure testsp1 (subtotal int, filters text, res out text)			
language plpgsql			
as $$			
declare			
var1 int;			
begin			
	res:='xxx' || filters;		
end; $$			

Пример процедуры с динамическим SQL 

CREATE OR REPLACE PROCEDURE insert_data(table_name TEXT, data TEXT) AS $$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(table_name) || ' VALUES (' || quote_literal(data) || ')';
END;
$$ LANGUAGE plpgsql;

Функции в PostgreSQL

Пример простой функции: 

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$	
DECLARE	
v_string ALIAS FOR $1;	
index ALIAS FOR $2;	
BEGIN	
-- some computations using v_string and index here	
END;	
$$ LANGUAGE plpgsql;	

Функция выдает некий набор: 

CREATE OR REPLACE FUNCTION return_customers() RETURNS SETOF s1.as_trace AS
$$
SELECT * FROM s1.as_trace
$$  LANGUAGE sql;