使用 awk 提取一对括号之间的文本

Question

我们有一个包含函数定义的 SQL 文件。我们想要读取此文件并准备另一个 SQL 文件，其中包含第一个 SQL 文件中所有函数的所有 drop DDL 语句。

例如，第一个 sql 的内容如下：

CREATE OR REPLACE FUNCTION folder_cycle_check (folder_key INTEGER, new_parent_folder_key INTEGER) RETURNS VOID AS $procedure$

DECLARE 
    parent_of_parent INTEGER;
BEGIN
    IF folder_key = new_parent_folder_key THEN
        RAISE EXCEPTION 'Illegal cycle detected',new_parent_folder_key;
    END IF;
SELECT INTO parent_of_parent  (SELECT parent_folder_key FROM folder where folder_key = new_parent_folder_key);

IF new_parent_folder_key IS NOT NULL THEN
    PERFORM folder_cycle_check(folder_key, parent_of_parent);
END IF;

END; $procedure$
LANGUAGE plpgsql;

现在我想将目标 SQL 文件创建为：

DROP FUNCTION folder_cycle_check((folder_key INTEGER, new_parent_folder_key INTEGER)

为了实现此目的，我有一个“genDrop.txt”文件，我将其与第一个 SQL 文件一起传递给 awk.exe 命令。 “genDrop.txt”的问题在于它仅生成带有 drop 语句的目标 SQL 文件：

DROP FUNCTION folder_cycle_check
which is not useful because PostgreSQL wants like this:
DROP FUNCTION folder_cycle_check(folder_key INTEGER, new_parent_folder_key INTEGER)

有人可以帮助我吗？我是 awk 编程的新手。 仅供参考，“genDrop.txt”是这样的：

#######################################################################
# AWK program to generate drop statements from create table, procedure, and view statements
############################################################################

function dropit(objtype, objname, rulename)
{
#   l[lines++] = "DROP " objtype " " objname " -- Line " NR ", Rule " rulename;
    l[lines++] = "DROP " objtype " " objname 
    next
}

function dropitpg(objtype, objname, funcargs, rulename)
{
#   l[lines++] = "DROP " objtype " " objname " -- Line " NR ", Rule " rulename;
    l[lines++] = "DROP " objtype " " objname " " funcargs
    next
}


BEGIN { FS="[ (;]*" }
# trim the line
{$2 = $2 }
# "grab creates" 
/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Pp][Rr][Oo][Cc]/             {dropit($3, $4, "CPs") }
/^[Cc][Rr][Ee][Aa][Tt][Ee] *[Pp][Rr][Oo][Cc]/               {dropit($2, $3, "CP") }

/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Pp][Rr][Oo][Cc]/ {dropit($5, $6, "CPs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Pp][Rr][Oo][Cc]/    {dropit($4, $5, "CP") }


/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Vv][Ii][Ee][Ww]/             {dropit($3, $4, "CVs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Vv][Ii][Ee][Ww]/                {dropit($2, $3, "CV") }

/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Vv][Ii][Ee][Ww]/ {dropit($5, $6, "CRVs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Vv][Ii][Ee][Ww]/    {dropit($4, $5, "CRV") }


/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Tt][Aa][Bb][Ll][Ee]/             {dropit($3, $4, "CTs") }
/^[Cc][Rr][Ee][Aa][Tt][Ee] *[Tt][Aa][Bb][Ll][Ee]/           {dropit($2, $3, "CT") }

/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Ss][Ee][Qq][Uu][Ee][Nn][Cc][Ee]/     {dropit($3, $4, "CSs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Ss][Ee][Qq][Uu][Ee][Nn][Cc][Ee]/    {dropit($2, $3, "CS") }

/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]/     {dropit($3, $4, "CSs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]/    {dropit($2, $3, "CS") }


END{
    print "-- Beginning " lines " drop statements"
    for (i = lines - 1; i >= 0; --i) {
    print l[i]
    print EOS
    print ""
    }
    print "-- End of " lines " drop statements"
}

BEGIN { FS="[ ;]*" }
/^ +[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]/     {dropitpg($5, $7, "CSs") }
/[Cc][Rr][Ee][Aa][Tt][Ee] *[Oo][Rr] *[Rr][Ee][Pp][Ll][Aa][Cc][Ee] *[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]/    {sed -nr "s/\s*\[([^\]+)\]/\1/p" }

END{
    print "-- Beginning " lines " drop statements"
    for (i = lines - 1; i >= 0; --i) {
    print l[i]
    print EOS
    print ""
    }
    print "-- End of " lines " drop statements"
}

Answer 1

如果您的示例输出（减去额外的 open-paren）就是您所需要的，那么我认为您的脚本太过了。这个怎么样？

#! /bin/awk -f
{
  if ($2 ~ /[Ff][Uu][Nn][Cc][Tt][Ii][Oo][Nn]/ ) {
      funcName=$3
      argSig=$0
      srchTarg= "^.*" funcName
      sub(srchTarg,"",argSig)
      # print "argSig=" argSig
      sub(/[\)].*$/, ")", argSig)
      # print "argSig=" argSig
      print "DROP FUNCTION " funcName argSig
    }
}

请务必 chmod 755 genDrop.awk

sample run

（我将示例输入的第一行更改为 ）

创建函数folder_cycle_check (....

示例运行

$ genDrop.awk dropFunction.txt
DROP FUNCTION folder_cycle_check (folder_key INTEGER, new_parent_folder_key INTEGER)

另外，将 awk 脚本命名为 genDrop.txt 无助于传达您的意图，当然您的意思是 genDrop.awk