Dynamic SQL - transition from ms to pg

On Mon, Jan 6, 2014 at 2:13 PM, Erik Darling <edarling80@gmail.com> wrote:

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.

As far as I can understand from your script, you are use 2 parameters: an
origin table and a target table. You could easily achieve that with a
grammar similar to the script you are referring to using some pl/pgsql
function, language which is pretty handy when generating queries on-the-fly
in a procedure like what you are looking for:
http://www.postgresql.org/docs/devel/static/plpgsql.html
Regards,
--
Michael

Hello

2014/1/6 Erik Darling <edarling80@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

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