Skip to content

Instantly share code, notes, and snippets.

@jatorre jatorre/gist:7401056
Created Nov 10, 2013

Embed
What would you like to do?
Removing the requirement for xml support on resolving tables function
-- Return an array of table names scanned by a given query
--
-- Requires PostgreSQL 9.x+
--
CREATE OR REPLACE FUNCTION CDB_QueryTables(query text)
RETURNS name[]
AS $$
DECLARE
exp JSON;
tables NAME[];
tab TEXT;
rec RECORD;
rec2 RECORD;
BEGIN
tables := '{}';
FOR rec IN SELECT CDB_QueryStatements(query) q LOOP
IF NOT ( rec.q ilike 'select %' or rec.q ilike 'with %' ) THEN
--RAISE WARNING 'Skipping %', rec.q;
CONTINUE;
END IF;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || rec.q INTO STRICT exp;
EXCEPTION WHEN others THEN
-- TODO: if error is 'relation "xxxxxx" does not exist', take xxxxxx as
-- the affected table ?
RAISE WARNING 'CDB_QueryTables cannot explain query: % (%: %)', rec.q, SQLSTATE, SQLERRM;
RAISE EXCEPTION '%', SQLERRM;
CONTINUE;
END;
---A bit of brute force here
with subq AS (
select (regexp_split_to_table(exp::text, 'Relation Name": "' )) as parts offset 1)
select array_agg(substring(parts from 0 for position('"' in parts))) FROM subq INTO STRICT tables;
RAISE DEBUG 'Tables: %', tables;
END LOOP;
-- RAISE DEBUG 'Tables: %', tables;
-- Remove duplicates and sort by name
IF array_upper(tables, 1) > 0 THEN
WITH dist as ( SELECT DISTINCT unnest(tables)::text as p ORDER BY p )
SELECT array_agg(p) from dist into tables;
END IF;
--RAISE DEBUG 'Tables: %', tables;
return tables;
END
$$ LANGUAGE 'plpgsql' VOLATILE STRICT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.