読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

PostgreSQLのfunction修正でsyntax error at or nearに悩んだ

DB プログラミング失敗談

環境

やったこと

こんな感じにファンクションをPostgreSQLに作成したとする。

create or replace function gettest1(numeric) returns varchar as
'
declare
  test_char varchar := ''AAA'';
begin
  return test_char;
end;
'
language 'plpgsql'
;

その後、A5:SQLなりなんなりでPostgreSQLに作成したファンクション(この例ではgettest1)を参照すると下記のようなコードが取得された。

CREATE FUNCTION public.gettest1(numeric) RETURNS varchar AS '
declare
  test_char varchar := 'AAA';--←ここ
begin
  return test_char;
end;
' LANGUAGE 'plpgsql'
;

そこで上記のコードをそのままもう一度PostgreSQLに対して実行したところ、下記のようなエラーが発生した。

syntax error at or near "AAA"

原因とか

リテラル文字列AAAがエスケープされていないために発生するエラー。

PostgreSQL初めて触るので今回知ったことなのだが、PostgreSQLのfunctionはOracleのそれとは異なり、ボディとなる部分(この例ではAS以降end;まで)を'(アポストロフィ)で囲わなければならない。よって、リテラル文字列をくくるアポストロフィにはエスケープのために二個必要となる。そして、PostgreSQLからfunctionを取得するとこのエスケープは外れるようで、そのまま再実行させるとコンパイルエラーになってしまう。

その他

A5:SQLのツールの問題かな、と思いpsqlで見てみた。

postgres-# \df+ gettest1
                                               List of functions
 Schema |   Name   | Result data type  | Argument data types |  Owner   | Language | Source code | Description 
--------+----------+-------------------+---------------------+----------+----------+-------------+-------------
 public | gettest1 | character varying | numeric             | postgres | plpgsql  | 
declare
  test_char varchar := 'AAA';
begin
  return test_char;
end;
            | 
(1 row)

が、やはりエスケープは外れる。ってことはRDBMSからfunctionのコード取得してそれを修正、みたいな使い方はお作法がよろしくないってことなんですかね? まぁエスケープが外れるのはそういうもんと言えばそういうもんかなーという気もするけど。