Databases
StackOverflow2013
Postsdbo
POextract text between a pair of parenthesis using awk
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POextract text between a pair of parenthesis using awk
    text
    copied!<p>We have an SQL file with function definitions. We want to read this file and prepare another SQL file with all the drop DDL statements for all the functions in the first SQL file.</p> <p>For example the first sql has the content like this:</p> <pre><code>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; </code></pre> <p>Now I want to create the target SQL file as:</p> <pre><code>DROP FUNCTION folder_cycle_check((folder_key INTEGER, new_parent_folder_key INTEGER) </code></pre> <p>For this to achieve I have a "genDrop.txt" file which I pass to awk.exe command along with the first SQL file. The problem with the "genDrop.txt" is that it is only generating the target SQL file with drop statements as:</p> <pre><code>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) </code></pre> <p>Can anybody help me? I am new to the awk programming. FYI, the "genDrop.txt" is this:</p> <pre><code>####################################################################### # 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 &gt;= 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 &gt;= 0; --i) { print l[i] print EOS print "" } print "-- End of " lines " drop statements" } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload