On Tue, Apr 18, 2017 at 09:03:49AM +0100, Roger Davies wrote:
> Just wondering if someone has a Postgres equivalent of
> greenbonde-scapdata-sync and greenbone-certdata-sync.
>
> The xsl SQL uses 'INSERT OR REPLACE" which is sqllite specific, so I get an
> error when running them.
Hi Roger,
It will be a variation on something similar I wrote in some other non OpenVAS
code where I needed a similar feature. It requires upsert support from PGSQL
9.5+.
Matthew.
NORMAL_COLUMNS = '''
SELECT
t.relname as table_name,
array_agg(DISTINCT a.attname) AS non_key_columns
FROM pg_class t
JOIN pg_index i ON t.oid = i.indrelid
JOIN pg_attribute a ON t.oid = a.attrelid AND a.attnum = ANY(i.indkey)
WHERE
t.oid = '%s'::regclass AND
--i.indisprimary = false AND
i.indisunique = false
GROUP BY
t.relname;
'''
UNIQUE_COLUMNS = '''
SELECT
ct.relname AS table_name,
ci.relname AS index_name,
array_agg(a.attname) AS unique_columns
FROM pg_class ct
JOIN pg_index i ON ct.oid = i.indrelid
JOIN pg_class ci ON ci.oid = i.indexrelid
JOIN pg_attribute a ON ct.oid = a.attrelid AND a.attnum = ANY(i.indkey)
WHERE
ct.oid = '%s'::regclass AND
ct.relkind = 'r' AND
i.indisprimary = false AND
i.indisunique = true
GROUP BY
ct.relname,
ci.relname;
'''
def partition_columns(args, db):
cursor = db.cursor()
cursor.execute(NORMAL_COLUMNS % args.table)
if cursor.rowcount != 1:
raise ValueError('incorrect number of rows')
_, normal_columns = cursor.fetchall()[0]
cursor.execute(UNIQUE_COLUMNS % args.table)
if cursor.rowcount != 1:
raise ValueError('incorrect number of rows')
_, _, unique_columns = cursor.fetchall()[0]
return unique_columns, [x for x in normal_columns if x not in
unique_columns]
...
sql = u'INSERT INTO %s (%s) VALUES\n%s' % (args.table, ',
'.join(escaped_fields), clauses)
uc = u', '.join(unique_columns)
nc = u', '.join([u'%s = EXCLUDED.%s' % (x, x) for x in normal_columns])
sql += u' ON CONFLICT (%s) DO UPDATE SET %s' % (uc, nc)
_______________________________________________
Openvas-discuss mailing list
[email protected]
https://lists.wald.intevation.org/cgi-bin/mailman/listinfo/openvas-discuss
