pg_get_ddl/get_ddl.sql at master · irumman/pg_get_ddl · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
CREATE OR REPLACE FUNCTION get_ddl(object_type varchar(10),object_name varchar(30))
RETURNS text
AS $$
  import os
  v_object_type =  object_type
  v_object_name = object_name
  v_dbname = 'postgres'
  v_schema = 'public'
  v_port = '5432'
  v_debug = 1

  global v_object_name
  global v_schema

  def debug_log(cmd):
    if v_debug == 1:
      cmd = cmd.replace("'","''")
      rv = plpy.execute("INSERT INTO log VALUES ('" + cmd + "')")

  def get_current_database():
    rv = plpy.execute("SELECT CURRENT_DATABASE() as dbname")
    dbname = rv[0]["dbname"]
    return dbname
  #def get_current_database():

  def get_connection_port():
    rv = plpy.execute("SHOW port")
    port = rv[0]["port"]
    return port

  def get_schema_name(object_name):
    global v_schema
    global v_object_name
    v_str =  str.split(object_name,'.')
    if len(v_str) == 1:
      v_schema = 'public'
      v_object_name = v_str[0]
      debug_log('Found user given object name with no dot(.): v_object_name = ' +  v_object_name + " and v_schema  = " + v_schema)
      return 1
    elif len(v_str) == 2:
      v_schema = v_str[0]
      v_object_name = v_str[1]
      debug_log('Found user given object name with two dots(.): v_object_name = ' +  v_object_name + " and v_schema  = " + v_schema)
      return 1
    else:
      debug_log('Found user given object name with more than two dots(.)')
      return 0
    #def get_schema_name():

  def run_command():
    o = ''

    cmd = 'pg_dump ' + v_dbname + ' -p ' + v_port + ' -s  -' + v_object_type + ' '+ object_name
    debug_log(cmd)

    stream = os.popen(cmd)
    for line in stream.readlines():
       if line == '\n' or line == '--\n' or ('PostgreSQL database dump' in line) or  ('SET' in line):
         continue
       o = o + line.replace(';\n',';\n\n')
    return  o
  #end of def run_command():

  def check_table_exists(v_object_name,v_schema):
    cmd = "SELECT relname as tablename FROM pg_stat_user_tables WHERE relname = '" + v_object_name + "' AND schemaname = '" + v_schema + "'"
    debug_log(cmd)
    rv = plpy.execute(cmd)

    if len(rv) > 0 :
      return 1
    else:
      return 0
  #end of def check_table_exists():

  # main()

  if v_object_type.lower() in ( 'table' ,'sequence','view'):
    v_object_type = 't'
    debug_log('Object is table and entered into the block')
    if get_schema_name(v_object_name) == 0:
      return 'Object name cannot be like "' + object_name
    debug_log('After get_schema_name: v_object_name = ' + v_object_name + " and  v_schema = " + v_schema)

    if v_object_type.lower() == 'table':
      if check_table_exists(v_object_name,v_schema) == 0 :
        return 'Table "' + object_name + '" does not exist in the database'


  v_dbname = get_current_database()
  global v_port
  v_port = get_connection_port()
  output = run_command()
  return output
$$ Language 'plpythonu';