Re: Dynamic SQL - transition from ms to pg

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

> 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
>>
>
>