Subjectively

dd if=/dev/random | kirk > blog

Subjectively header image 1

DFWPUG Meetup April 2, 2014

March 14th, 2014 · PostgreSQL

Just a quick reminder that Dallas/Fort Worth PostgreSQL Users Group has a Meetup the first Wednesday of every month.

What: 13 Calm Years of PostgreSQL in Critical Messaging

Who: John Scott

When: Wednesday, April 2, 2014 7:00 PM

Where:
Improving Enterprises
16633 Dallas Parkway Suite 110 Addison, TX 75001

DFW PUG on Meetup

→ No CommentsTags:

DFW PUG Meetup for March 5, 2014

February 28th, 2014 · PostgreSQL

Just a quick reminder that Dallas/Fort Worth PostgreSQL Users Group has a Meetup next week.

What: Talk about PostgreSQL and column based data storage

When: Wednesday, March 5, 2014 7:00 PM

Where:
Improving Enterprises
16633 Dallas Parkway Suite 110 Addison, TX 75001

DFW PUG on Meetup

→ No CommentsTags:

create a list of delete statements that follow referential integrity in PostgreSQL v2

November 26th, 2013 · Uncategorized

This is an update to a previous post.  The script now supports multiple schema.

create a list of delete statements that follow referential integrity in PostgreSQL

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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
#!/bin/bash - 
#===============================================================================
#
#          FILE: generate_delete
# 
#         USAGE: ./generate_delete 
# 
#   DESCRIPTION: Takes a table name and a list of values
# 			and creates a list of delete statements
#			that follow referential integritY
# 
#       OPTIONS: ---
#  REQUIREMENTS: ---
#          BUGS: ---
#         NOTES: V2 Now with schema support
#        AUTHOR: Kirk Roybal (), kirk@webfinish.com
#  ORGANIZATION: 
#       CREATED: 09/25/2013 11:23
#      REVISION:  ---
#===============================================================================
 
set -o nounset                              # Treat unset variables as an error
 
ScriptVersion="2.0"
 
#===  FUNCTION  ================================================================
#         NAME:  usage
#  DESCRIPTION:  Display usage information.
#===============================================================================
function usage ()
{
	cat <<- EOT
 
  Usage :  ${0##/*/} [options] -t application -q 1024,1055,1077 -m dpggen -u kroybal -r ods [--] 
 
  Options: 
  -d|debug      Bash Debugging Info
  -m|machine	Database host machine
  -t|table	Starting table
  -u|user	User to connect to DB
  -q|quals	Qualifying records to delete
  -r|relation	Database name
  -s|schema     Logical Partition
  -h|help       Display this message
  -v|version    Display script version
 
	EOT
}    # ----------  end of function usage  ----------
 
#-----------------------------------------------------------------------
#  Handle command line arguments
#-----------------------------------------------------------------------
 
[[ $# -eq 0 ]] && {
	# no arguments
	usage
	exit 1
}
 
user=$USER
host=localhost
db=$USER
schema=public
 
while getopts ":dhm:q:r:s:t:u:v" opt
do
  case $opt in
 
    d|debug    )  set -x;;
    h|help     )  usage; exit 0   ;;
    t|table    )  table=$OPTARG;;
    q|quals    )  quals=$OPTARG;;
    m|machine  )  host=$OPTARG;;
    u|user     )  user=$OPTARG;;
    r|relation )  db=$OPTARG;;
    s|schema   )  schema=$OPTARG;;
    v|version  )  echo "$0 -- Version $ScriptVersion"; exit 0   ;;
 
    \? )  echo -e "\n  Option does not exist : $OPTARG\n"
          usage; exit 1   ;;
 
  esac    # --- end of case ---
done
shift $(($OPTIND-1))
 
query() {
	psql -h $host -U $user $db -qtAc "$1"
}
 
find_oid() {
 
sql="SELECT c.oid
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^($1)$'
      AND nspname ~ '^($2)$'
  --AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 
  n.nspname, c.relname;"
 
query  "$sql"
 
 
}
 
find_deps() {
 
sql="SELECT conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '$1' AND c.contype = 'f' ORDER BY conname"
 
query "$sql"
 
}
 
find_pk() {
 
sql="SELECT
c.column_name --, c.data_type
FROM
information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
where constraint_type = 'PRIMARY KEY' and tc.table_name = '$1' AND tc.constraint_schema = '$2';"
 
query "$sql"
 
}
 
find_tables() {
 
IFS=$'\n'
for dep in $(find_deps "$oid")
do
	# dep:   risk.report|FOREIGN KEY (document_id) REFERENCES document(id) ON UPDATE RESTRICT ON DELETE RESTRICT
	table=$(echo $dep|cut -d"|" -f1)
	# table: risk.report
	schema=$(echo $table | cut -d. -f1)
	# schema: risk
	table=$(echo $table | cut -d. -f2)
	# table: report
	fk=$(echo $dep|cut -d"|" -f2)
	#fk: FOREIGN KEY (document_id) REFERENCES document(id) ON UPDATE RESTRICT ON DELETE RESTRICT
	fkid=$(echo $fk | sed 's/.*KEY (\(.*\)) REF.*/\1/')
	#fkid: document(id)
	pk=$(find_pk "$table" "$schema")
	# id
	sql="DELETE FROM ${schema}.${table} WHERE $fkid IN ($3);"
	# add to the array
	sql_list+=("$sql")
	oid=$(find_oid "$table" "$schema")
	#recurse until we run out of dependencies
	[[ -z $oid ]] || find_tables "$oid" "$pk" "SELECT $pk FROM ${schema}.${table} WHERE $fkid IN ($3)"
done
 
}
 
oldifs=$IFS
IFS=$'\n'
oid=$(find_oid "$table" "$schema")
[[ -z $oid ]] && exit 1 #couldn't find top level dependency
pk=$(find_pk "$table" "$schema")
sql="DELETE FROM ${schema}.${table} WHERE $pk IN ($quals);"
sql_list+=($sql)
find_tables "$oid" "$pk" "SELECT $pk FROM ${schema}.${table} WHERE $pk IN ($quals)"
echo "BEGIN"
for (( i=${#sql_list[@]}-1;i>=0;i-- ))
do
	echo "${sql_list[${i}]}"
done
echo "END"
IFS=$oldifs

→ No CommentsTags: