[PostgreSQL] 프로시저 vs 함수

# Function 

 

값을 반드시 반환한다

기능을 정의한 것

select 호출가능함

주로 클라이언트단에서 실행

 

# Procedrue

 

값을 필요에 따라 반환한다

서버에서 실행해 처리속도가 빠름

미리 컴파일 된 sql 명령 집합이다. 

porstgresql 12 procedure 등장 

 

 

---

## Function

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;

---

DROP FUNCTION [ IF EXISTS ] 이름 ( [ [ 변수종류 ] [ 변수명 ] 변수자료형 [, ...] ] )

    [ CASCADE | RESTRICT ]

 

CREATE OR REPLACE FUNCTION totalRecords ()

RETURNS integer AS $total$

declare

total integer;

BEGIN

   SELECT count(*) into total FROM COMPANY;

   RETURN total;

END;

$total$ LANGUAGE plpgsql;


---


create or replace function addnumber(a INTEGER, b INTEGER)

returns INTEGER as $$

begin 

return a+b;

end;

$$ language PLPGSQL;

---

 

## Procedrue 

CREATE [ OR REPLACE ] PROCEDURE

    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )

  { LANGUAGE lang_name

    | TRANSFORM { FOR TYPE type_name } [, ... ]

    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

    | SET configuration_parameter { TO value | = value | FROM CURRENT }

    | AS 'definition'

    | AS 'obj_file', 'link_symbol'

  } ...

create or replace procedure insert_data(a integer, b integer)

language sql

as $$

INSERT INTO tbl VALUES (a);

INSERT INTO tbl VALUES (b);

$$;

call insert_data(1,2);

---

create or replace procedure insert_data(a integer, b integer)

language plpgsql

as $$

declare 

begin

create table test(id int);

insert into test values(a);

insert into test values(b);

-- rollback;  create 문까지 사라짐.

end;

$$;

 

 

 

** https://www.enterprisedb.com/postgres-tutorials/10-examples-postgresql-stored-procedures

** https://dba.stackexchange.com/questions/2357/what-are-the-differences-between-stored-procedures-and-stored-functions