Re: Dynamic SQL - transition from ms to pg

On Mon, Jan 6, 2014 at 2:13 PM, Erik Darling <edarling80(at)gmail(dot)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