Hello
2014/1/6 Erik Darling <edarlin...@gmail.com> > Hi, > > I've been developing for MS SQL around four years. I'm starting out with > some work in Postgresql next week, and I'd like to know if there's any > equivalent way to do something like this (from my word press) > > http://sqldriver.wordpress.com/2013/12/09/generating-inserts-dynamically/ > > My question is mainly about creating comma delimited column names as > variables and executing dynamic SQL with them. I've spent some time trying > to find an answer and I seem to keep running into the same few stack > questions. > > Any advice is appreciated. I think I'm going to end up needing dynamic > queries like what I've written for similar tasks moving data from files to > staging tables and then to a larger set of data warehouse tables and > setting up either views (perhaps materialized?) or more tables for > reporting. > It can look some like CREATE OR REPLACE FUNCTION sample_insert_noflag(table_from text, table_to text, query_filter text) RETURNS void AS $$ DECLARE sql text; column_names text; BEGIN column_names = (SELECT string_agg(quote_ident(t.column_name), ',') FROM information_schema.tables t WHERE t.table_name = table_from AND t.column_name <> 'STATUSFLAG'); sql := format('INSERT INTO %I(%s) SELECT %s FROM %I %s', table_to, column_names, table_from, query_filter); RAISE NOTICE '%', sql; EXECUTE sql; RETURN; END; $$ LANGUAGE plpgsql STRICT; Regards Pavel Stehule > Thanks, > Erik >
