[PostgreSQL] FUNCTION 예시

FUNCTION 예제
참고 : PostgreSQL 7.2 programmer's Guide 중 12장
-----------------------------------------------------------------------------------------------
CREATE FUNCTION "TP1"(integer , numeric) RETURNS integer AS $$
 UPDATE bank SET balence = balance - $2 WHERE accountno = $1;
 SELECT 1;
$$ LANGUAGE SQL;

SELECT "TP1"(17 , 100.00);
※  FUNCTION 명은 "" 이 없을때는 소문자로 인식 됨 대문자로 인식을 시키려면
   "" 사용 해야함.
------------------------------------------------------------------------------------------------
CREATE FUNCTION TP1(integer , numeric) RETURNS numeric AS $$
 UPDATE bank SET blance = balance - $2 WHERE accountno = $1;
 SELECT balance FROM bank WHERE accountno = $1;
$$ LANGUAGE SQL;
------------------------------------------------------------------------------------------------
CREATE FUNCTION clean_EMP() RETURNS integer AS $$
 DELETE FROM EMP WHERE EMP.salary <= 0;
        SELECT 1 AS ignore_this;
$$ LANGUAGE SQL;
------------------------------------------------------------------------------------------------
CREATE FUNCTION one() RETURNS integer AS $$
 SELECT 1 as RETURNS;
$$ LANGUAGE SQL;
------------------------------------------------------------------------------------------------
CREATE FUNCTION listchildren(TEXT) RETURNS SETOF text AS $$
 SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL;
------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS

DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

 

SELECT * FROM getallfoo();

------------------------------------------------------------------------------------------------

 

create function editproc(uid varchar , pwdVal varchar , seqVal integer)
returns table(seq integer ,thread integer , writer varchar , email varchar , title varchar , mode smallint , readcount int , content text ) as $$
declare passcheck boolean;
begin
 passcheck := check_password($1, $2, $3);
 if passcheck = true then
  return query select b.seq , b.thread , b.writer , b.email , b.title , b.mode , b.readcount , b.content from board as b where b.seq=$3;
 else
  return query(select '해당 레코드가 없습니다.');
 end if;
end;
$$ language plpgsql

------------------------------------------------------------------------------------------------

-- 데이터가 필드에 존재 하는지 여부
CREATE FUNCTION dataSearch(code varchar(10)) RETURNS int4 AS $$
 DECLARE
 result integer = 0;
 r_val integer =0;
 BEGIN
 select count(*) from tbStore into result where s_code = $1;
 if result = 1 then
     r_val = 1; 

 else
     r_val = 0;  

 end if;

 return r_val;
 END;
$$ LANGUAGE plpgsql;

 

------------------------------------------------------------------------------------------------

--  테이블에 저장 하기 위해 작성한 function

CREATE FUNCTION dateInsert(a varchar(50), b varchar(10), c varchar(14), d varchar(80) , e varchar(10) , OUT result integer ) RETURNS int4 AS $$
 BEGIN 
       INSERT into tbStore(s_stname , s_code , s_tel , s_address , s_name)
       VALUES(a ,b , c , d , e)
       RETURNING s_code into result;
 END;       
$$ LANGUAGE plpgsql;