Re: Dynamic SQL - transition from ms to pg

Hello

2014/1/6 Erik Darling <edarling80(at)gmail(dot)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
>