distal-attribute
distal-attribute
distal-attribute
distal-attribute

RE: bug + bugfix for postgres - matching tables with the same name in all other schemas:

weinerk posted 2 months ago in General

RE: bug + bugfix for postgres - matching tables with the same name in all other schemas:

change this:

WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class WHERE pg_class.relname = E'XXXTableName' AND pg_class.oid = pg_index.indrelid ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);

to this:

WITH ndx_list AS ( SELECT pg_index.indexrelid, pg_class.oid FROM pg_index, pg_class INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE pg_class.relname = E'XXXTableName' AND pg_class.oid = pg_index.indrelid AND pg_namespace.nspname = E'XXSchemaName' ), ndx_cols AS ( SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN E'PRIMARY' ELSE CASE i.indisunique WHEN true THEN E'UNIQUE' ELSE E'KEY' END END AS CONSTRAINT_TYPE, pg_class.oid FROM pg_class JOIN pg_index i ON (pg_class.oid = i.indexrelid) JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid) )SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_attribute a JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid);

otherwise it matches tables with the same name in all other schemas

Please login to leave a reply, or register at first.