Преобразование кода PL/SQL на plpgsql

Материал из PersonallWiki
Перейти к: навигация, поиск

Особенности, выявленные по-ходу разработки и переноса кода[править]

1.Индексы массивов всегда начинаются с 1.

2.При конкатенации строк значение NULL||’string’ = NULL, поэтому надо делать ‘’||’string’

3.При формировании текстовых строк, если один из элементов конкатенации IS NULL вся строка будет NULL !!! Поэтому везде надо пихать COALESCE( var, ‘’)

4.При вызове функции из блока SQL в функции нужно ставить параметр STABLE, иначе будут жуткие тормоза !!! Выявлено на 9.3.4.

5.При создании индексов по текстовым полям в базе с кодировкой UTF8 нужно применять доп. параметр text_pattern_ops. Например, так:

CREATE INDEX ON foo(c2 text_pattern_ops);

6. Адрес дебаггера для plpg - https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

7. После добавления своих библиотек в /etc/ld.conf.d нужно выполнить ldconfig

Пожелания[править]

1.Добавить режим FORCE при создании VIEW

2.ОЧЕНЬ нужны нормальные временные таблицы, которые не уничтожаются после завершения сеанса !!!!!!!!!!!!!!!

3.Добавить возможность менять атрибуты в таблице, даже если эти атрибуты встречаются в представления. Сейчас получается, что невозможно изменить атрибут (простую длину), пока этот атрибут встречается в запросе представлений. Приходится удалять эти представления, менять таблицу, и восстанавливать view!!!

4.Добавить аналог v$session_longops для отслеживания степени выполнения запросов.

Таблица перекодировки[править]

Конструкция

Oracle

Plpgsql

Особенности

1.      

Типы данных

Нужно сделать таблицу перекодировки

2.      

Пакетные константы

$1 CONSTANT $type := $val;

Для каждого пакета сделать метод возвращающий константу в виде функции

3.      

Пакетные переменные

$1 $type := $val; $comment

Sv|Gv( ‘$pkg_name.$1’, $val|$type)

4.      

Вызов функции без параметров

$1

$1()

5.      

Вызов процедуры

$1

PERFORM $1()

6.      

Вызов функции с OUT параметрами

:result  := FUNCTION(:OUT_PARAM1,

                                          :OUT_PARAM2 );

SELECT *

INTO

OUT_PARAM1, OUT_PARAM2, result

FROM FUNCTION ();

7.      

sysdate

SYSDATE

LOCALTIMESTAMP

А лучше использовать statement_timestamp() - время начала выполнения ЗАПРОСА, clock_timestamp() - время меняется даже ВНУТРИ одного запроса SELECT, поэтому индекс по дате не ИСПОЛЬЗУЕТСЯ!!!   ,

Так как она работает независимо от транзакции.

8.      

sysdate +/- N

SYSDATE +/- N

LOCALTIMESTAMP $1 interval '$2 days'

$1 = + / -

$2 = N

9.      

10.  

from dual

FROM DUAL

удаляем

11.  

:new.

:NEW.

NEW.

В триггерах

12.  

:old.

:OLD.

OLD.

В триггерах

13.  

Тип операции в триггере

INSERTING|DELETING|UPDATING

TG_OP = 'INSERT'|'DELETE'|'UPDATE'

В триггерах

14.  

Динамический SQL

EXECUTE IMMEDIATE

EXECUTE

15.  

sequence_name.nextval

SEQUENCE_NAME.NEXTVAL

nextval('sequence_name')

16.  

sequence_name.currval

SEQUENCE_NAME.CURRVAL

currval('sequence_name')

17.  

MINUS

MINUS

except

18.  

DBMS_OUTPUT

DBMS_OUTPUT.put_line|put|new_line

raise_output($2)

19.  

PROCEDURE

PROCEDURE

FUNCTION

20.  

raise_application_error

RAISE_APPLICATION_ERROR

raise exception $1

21.  

DEFAULT NULL

DEFAULT NULL

Удаляем

22.  

dup_val_on_index

dup_val_on_index

unique_violation

23.  

invalid_number

invalid_number

invalid_text_representation

24.  

CURSOR

CURSOR $1

$1 CURSOR

25.  

CURSOR IS SELECT

CURSOR IS SELECT

$1  CURSOR $2 FOR SELECT

cObjRelOne CURSOR ( iObjId numeric)  FOR

   SELECT

26.  

TO_DATE

TO_DATE

to_date($1,$2)

27.  

add_months

ADD_MONTHS

$1 + '$2 month'::interval

28.  

add_years

ADD_YEARS

$1 + '$2 year'::interval

29.  

NO_DATA_FOUND

SELECT $1 INTO $2 $3

EXCEPTION WHEN NO_DATA_FOUND

 SELECT $1 STRICT $2 $3

EXCEPTION WHEN NO_DATA_FOUND

30.  

WHERE ROWNUM <= 1

WHERE|AND  ROWNUM = | <= 1

LIMIT 1

31.  

IS NULL

IS NULL

coalesce($1::text, ) =

По идее, только для текстовых

32.  

IS NOT NULL

IS NOT NULL

($1 IS NOT NULL AND $1::text <> )

33.  

SQLCODE

SQLCODE

sqlstate

ОСОБЕННОСТЬ!

Обращаться к переменным ошибок можно только в специальной секции EXCEPTION

34.  

SQLERRM

SQLERRM

SQLERRM

35.  

EXIT WHEN $1%NOTFOUND

EXIT WHEN $1%NOTFOUND

IF NOT FOUND THEN EXIT; END IF;

36.  

SQL%NOTFOUND

SQL%NOTFOUND

NOT FOUND

37.  

REF CURSOR

IS REF CURSOR

REFCURSOR

38.  

SYS_REFCURSOR

SYS_REFCURSOR

REFCURSOR

39.  

REGEXP_LIKE

REGEXP_LIKE  (string, pattern)

string ~ pattern

40.  

REGEXP_SUBSTR

REGEXP_SUBSTR

Substring ($1 , $2)

Шаблон работает немного по-другому. В результат берутся данные из скобок, а не все совпавшее выражение.

41.  

PIPE ROW

PIPE ROW

RETURN NEXT rrrow

42.  

SUBSTR

SUBSTR(STRING, START_POSITION, LENGTH)

substring($1 from $2 for $3)

SUBSTR в чистом виде тоже работает

43.  

decode

DECODE($1, $2,$3 ,NULL)

CASE WHEN $1=$2 THEN $3 ELSE $4 END

44.  

BULK COLLECT INTO

BULK COLLECT INTO

SELECT ARRAY(SELECT $col FROM TABLE)

INTO $array_var;

45.  

WHERE $col IN (

SELECT * FROM $nested_table)

WHERE $col IN (

SELECT * FROM $nested_table)

WHERE $col IN (SELECT * FROM unnest($nested_table))

$nested_table = ARRAY

46.  

CONNECT BY PRIOR

id = PARENT                     

SELECT *

FROM application ap

START WITH ap.PARENT = 0

CONNECT BY PRIOR id = PARENT

    WITH RECURSIVE class_tree (LVL, class_id, parent_id, NAME) as (

      SELECT 1 as LVL, c.class_id, c.parent_id, c.NAME

      FROM srcadm.class c

      WHERE class_id = 18

    UNION ALL

      SELECT LVL+1 as LVL, c.class_id, c.parent_id, c.NAME

      FROM srcadm.class c

      JOIN class_tree ct

ON (ct.class_id  = c.parent_id )

    )

    SELECT *

    FROM class_tree c

Рекурсия сверху вниз

47.  

CONNECT BY PRIOR

id = PARENT                     

    WITH RECURSIVE class_tree (LVL, class_id, parent_id, NAME) as (

      SELECT 1 as LVL, c.class_id, c.parent_id, c.NAME

      FROM srcadm.class c

      WHERE class_id = 1

    UNION ALL

      SELECT LVL+1 as LVL, c.class_id, c.parent_id, c.NAME

      FROM srcadm.class c

      JOIN class_tree ct

ON (ct.parent_id = c.class_id )

    )

    SELECT *

    FROM class_tree c

Рекурсия снизу вверх

48.  

DBMS_LOCK.SLEEP

DBMS_LOCK.SLEEP ( 5 );

PERFORM pg_sleep(5);

49.  

userenv('sessionid')

userenv('sessionid')

pg_backend_pid()

50.  

SQL%ROWCOUNT

SQL%ROWCOUNT

GET DIAGNOSTICS rows_count = row_count; ()

DECLARE

   rows_count  integer;
 

BEGIN


 GET DIAGNOSTICS rows_count = row_count;    
     
 IF rows_count = 0 THEN     -- IF SQL%ROWCOUNT = 0 THEN
   ...
 END IF;

51.  

ROWNUM AS pos

ROWNUM AS pos

row_number() over() AS pos()