Thanks Matthew
I'm not an SQL specialist, it's been a while since I did any SQL. but I'll
see what I can come up with.
I am using Postgres 9.5+, so the upsert suggestion is also useful.
Roger
2017-04-21 22:19 GMT+01:00 Matthew Hall <mh...@mhcomputing.net>:
> 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
Openvas-discuss@wald.intevation.org
https://lists.wald.intevation.org/cgi-bin/mailman/listinfo/openvas-discuss
