Safe Pole `INSERT INTO t1 SELECT * FROM ...`?

Question

Is there a way to make INSERT INTO t1 SELECT * FROM... so that it fails if the column names do not match?

I am using Postgresql 9.x Column names are not known in advance.

Motivation: I periodically update materialized views using the (quite standard) PL / pgSQL procedure:

 CREATE OR REPLACE FUNCTION matview_refresh(name) RETURNS void AS $BODY$ DECLARE matview ALIAS FOR $1; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF NOT FOUND THEN RAISE EXCEPTION 'Materialized view % does not exist.', matview; END IF; EXECUTE 'TRUNCATE TABLE ' || matview; EXECUTE 'INSERT INTO ' || matview || ' SELECT * FROM ' || entry.v_name; UPDATE matviews SET last_refresh=CURRENT_TIMESTAMP WHERE mv_name=matview; RETURN; END 

I preferred a TRUNCATE and then SELECT * INTO instead of DROP / CREATE because it seemed lighter and more compatible over time. This will not work if someone adds / removes columns from the view (then I would do DROP / CREATE), but it does not matter, in this case the update will not complete, and we will catch the problem soon. The important thing is what happened today: someone reordered the two columns of the view (of the same type) and updated the dummy data.

Answer 1

Create this in your plpgsql function to make sure that the view and table share the same column names in the same sequence:

 IF EXISTS ( SELECT 1 FROM ( SELECT * FROM pg_attribute WHERE attrelid = matview::regclass AND attisdropped = FALSE AND attnum > 0 ) t FULL OUTER JOIN ( SELECT * FROM pg_attribute WHERE attrelid = entry.v_name::regclass AND attisdropped = FALSE AND attnum > 0 ) v USING (attnum, attname) -- atttypid to check for type, too WHERE t.attname IS NULL OR v.attname IS NULL ) THEN RAISE EXCEPTION 'Mismatch between table and view!'; END IF; 

FULL OUTER JOIN adds a NULL for any mismatch between the list of column names. So, if EXISTS finds a string, something is not working.

And casting to ::regclass will immediately throw an exception if any table or view does not exist (or outside the scope - not in search_path , and not as a schema).

If you also want to check the column data types, just add atttypid to the USING .

Aside: querying pg_catalog tables is regularly faster by an order of magnitude than querying for bloated views int information_schema - information_schema is only good for keeping SQL standard and code portable. Since you are writing 100% Postgres specific code, not here.

Answer 2

To get the columns in the correct order, you can query schema information. columns:

 SELECT INTO cols array_to_string(array_agg(column_name::text), ',') FROM ( SELECT column_name FROM information_schema.columns WHERE table_name = 'matview' ORDER BY ordinal_position ) AS x; EXECUTE 'INSERT INTO ' || matview || ' SELECT ' || cols || ' FROM ' || entry.v_name; 

You can get a list of columns directly from pg_attribute - just replace the internal SELECT with information_schema.columns with:

 SELECT attname AS column_name FROM pg_attribute WHERE attrelid = 'matview'::regclass AND attisdropped = false ORDER BY attnum;