Thank you Pavel. That's exactly what I needed to get started. On Jan 6, 2014 3:25 AM, "Pavel Stehule" <pavel.steh...@gmail.com> wrote:
> 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 >> > >
