Re: Dynamic SQL - transition from ms to pg

[Michael Paquier <michael.paquier@xxxxxxxxx>]

On Mon, Jan 6, 2014 at 2:13 PM, Erik Darling <edarling80@xxxxxxxxx> 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