This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle® to PostgreSQL.
PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, conditionals are similar. The main differences you should keep in mind when porting from PL/SQL to PL/pgSQL are:
There are no default values for parameters in PostgreSQL.
You cannot use parameter names that are the same as columns that are referenced in the function. Oracle allows you to do this if you qualify the parameter name using function_name.paramater_name.
You can overload function names in PostgreSQL. This is often used to work around the lack of default parameters.
No need for cursors in PL/pgSQL, just put the query in the FOR statement. (See Example 36-6.)
In PostgreSQL the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. See Section 36.2.1.
Instead of packages, use schemas to organize your functions into groups.
Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.
예제 1 Example 36-5. Porting a Simple Function from PL/SQL to PL/pgSQL
Here is an OraclePL/SQL function:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURN varchar IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
show errors;
Let's go through this function and see the differences compared to PL/pgSQL:
The RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL. Also, IS becomes AS, and you need to add a LANGUAGE clause because PL/pgSQL is not the only possible function language.
In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it. This substitutes for the terminating / in the Oracle approach.
The show errors command does not exist in PostgreSQL, and is not needed since errors are reported automatically.
This is how this function would look when ported to PostgreSQL:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURNS varchar AS $$
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;
1) RETURN -> RETURNS
2) IS -> AS
3) $$ LANGUAGE plpgsql;
4) FUNCTION BODY가 LITERAL로 구성되어져있어서 ' 나 $가 필요에 의해 쓰임
5) show errors command does not exist in PostgreSQL
음..대~~충 요런식으로 바뀌었음을 알수가 있다.
예제 2 Example 36-6. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL
The following procedure grabs rows from a SELECT statement and builds a large function with the results in IF statements, for the sake of efficiency. Notice particularly the differences in the cursor and the FOR loop.
This is the Oracle version:
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_cmd VARCHAR(4000);
BEGIN
func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
FOR referrer_key IN referrer_keys LOOP
func_cmd := func_cmd ||
' IF v_' || referrer_key.kind
|| ' LIKE ''' || referrer_key.key_string
|| ''' THEN RETURN ''' || referrer_key.referrer_type
|| '''; END IF;';
END LOOP;
func_cmd := func_cmd || ' RETURN NULL; END;';
EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;
Here is how this function would end up in PostgreSQL:
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
referrer_key RECORD; -- declare a generic record to be used in a FOR
func_body text;
func_cmd text;
BEGIN
func_body := 'BEGIN';
-- Notice how we scan through the results of a query in a FOR loop
-- using the FOR <record> construct.
FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
func_body := func_body ||
' IF v_' || referrer_key.kind
|| ' LIKE ' || quote_literal(referrer_key.key_string)
|| ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
|| '; END IF;' ;
END LOOP;
func_body := func_body || ' RETURN NULL; END;';
func_cmd :=
'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
v_domain varchar,
v_url varchar)
RETURNS varchar AS '
|| quote_literal(func_body)
|| ' LANGUAGE plpgsql;' ;
EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;
IF ELSE http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html
작성한 function - 예외처리는 하지 않았다.(간단한 예제로 만들어보았다.)
CREATE OR REPLACE FUNCTION update_status_proc2() RETURNS void AS $$
DECLARE
ri acet_table%ROWTYPE; -- 어떤 Table에 접근 할 것인가??(acet_table)
sc TEXT;
BEGIN
FOR ri IN -- for 문 사용
SELECT * FROM fwrequest
WHERE exp_date - interval '7 day' < current_timestamp
AND status = '신청완료'
UNION
SELECT * FROM fwrequest
WHERE exp_date < current_timestamp
AND status ='만료예정'
LOOP
IF ri.status = '신청완료' THEN
sc := '만료예정';
ELSE
IF ri.status = '만료예정' THEN
sc := '기한만료';
END IF;
END IF;
UPDATE fwrequest SET status=sc -- 여기에서는 status=sc와 같이 :=가 아닌 = 이다.
WHERE sequence = ri.sequence AND ktid = ri.ktid AND status = ri.status;
END LOOP;
END
$$ LANGUAGE plpgsql;
팁!]
만든 Function 실행하기!!
실행은 컴파일 -> 실행이 되겠다.
1) 아래와 같이 작성을 한 뒤에 F5(실행)을 시키면 컴파일이 된다.
2) 아래와 같이 SELECT function 이름(); 을 한 뒤 pgScript 수행(F6)을 누르면 수행이 되어진다.
3) 간단히 select를 날려 확인하면 된다.
FOR 말고도 FECHE, CURSOR 등 다양한 반복적인 행위를 하는 것들이 있다.
오라클에서는 CURSOR를 많이 썼었는데 여기에서는 FOR를 한번 써보았다.
