main
define lv_dbname char(64)
define lv_query char(512)
define lv_tabname char(64)
define lv_colsrch char(512)
define lv_colname,lv_coltype, lv_default char(256)
define lv_sql char(1024)

if num_args() != 1 then
	display "Error Usage : ",arg_val(0) clipped," dbname"
	exit program 1
end if

if fgl_getenv("A4GL_SQLTYPE")!="pg8" then
	display "You must set A4GL_SQLTYPE=pg8 before running this program"
	exit program 1
end if



let lv_dbname=arg_val(1)

run "createlang -d "|| lv_dbname clipped||" plpgsql"

database lv_dbname


whenever error continue
execute immediate "CREATE LANGUAGE plpgsql;"
whenever error stop




# These two functions last_serial & set_last_serial_val are required for A4GL_LEXTYPE=EC handling



execute immediate lv_sql
let lv_colsrch= "SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)   FROM pg_catalog.pg_attribute a,pg_class b WHERE a.attrelid = b.oid AND a.attnum > 0 AND NOT a.attisdropped AND b.relname=? and pg_table_is_visible(b.oid) ORDER BY a.attnum"

prepare p_srch from lv_colsrch

let lv_query=" SELECT c.relname FROM pg_catalog.pg_class c",
     " LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner",
     " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace",
     " WHERE c.relkind IN ('r','')",
     " AND n.nspname NOT IN ('pg_catalog', 'pg_toast')",
     " AND pg_catalog.pg_table_is_visible(c.oid)",
     "  ORDER BY 1"

prepare p_q from lv_query

display "Scanning tables... please wait"
declare c_gettables cursor for p_q
foreach c_gettables into lv_tabname

	declare c_get_serials cursor for p_srch
	foreach c_get_serials using lv_tabname into lv_colname,lv_coltype,lv_default
		#display "   lv_colname", lv_colname clipped, " ", lv_coltype
		if lv_coltype matches "int*" or lv_coltype="bigint" then
			if lv_default matches "*nextval*" then
				call create_serial(lv_tabname, lv_colname,lv_default)
			end  if
		end if
	end foreach
end foreach

end main


function create_serial(lv_tabname,lv_colname,lv_default) 
define lv_tabname,lv_colname char(64)
define lv_default char(128)
define lv_str char(1024)
define lv_seq char(512)

define s1 integer
define s2 integer
define a integer

# Lets try to find out sequence..
let s1=0
let s2=0
for a=1 to length(lv_default)
	if lv_default[a]="'" then
		if s1=0 then
			let s1=a+1
		else
			if s2=0 then
				let s2=a-1
			else
				# got confused
				display "Could not create trigger for ",lv_tabname clipped,".",lv_colname clipped," - didn't understand : ",lv_default clipped
				return
			end if
		end if
	end if
end for

if s1=0 or s2=0 then
	display "Could not create trigger for ",lv_tabname clipped,".",lv_colname clipped," - didn't understand : ",lv_default clipped
	return
end if


display "Creating trigger/function for ", lv_tabname clipped,".", lv_colname clipped, " <",lv_default clipped,">"
let lv_seq=lv_default[s1,s2]
let lv_str=
	"CREATE OR REPLACE FUNCTION get_next_", lv_tabname clipped,"_",lv_colname clipped,"() RETURNS TRIGGER AS $$\n", 
        "BEGIN\n ",
        "   IF NEW.",lv_colname clipped," = 0 then\n",
        "      SELECT nextval('",lv_seq clipped,"') into NEW.",lv_colname clipped,";\n",
        "   ELSE\n",
        "      IF NEW.",lv_colname clipped," > 0 THEN\n",
        "         PERFORM setval('", lv_seq clipped,"',NEW.",lv_colname clipped,");\n",
        "   END IF;\n",
        "END IF;\n",
        "RETURN NEW;\n",
        "END;\n",
        "$$ LANGUAGE plpgsql\n"

display lv_str clipped
execute immediate lv_str

whenever error continue
let lv_str="drop trigger sertrig_",lv_tabname clipped,"_",lv_colname clipped, " on ", lv_tabname clipped
display lv_str clipped
execute immediate lv_str
whenever error stop

let lv_str="create trigger sertrig_",lv_tabname clipped,"_",lv_colname clipped," before insert on ",lv_tabname clipped," for each row execute procedure get_next_", lv_tabname clipped,"_",lv_colname clipped, "();"

display lv_str clipped
execute immediate lv_str

end function