Postgres Pro
Downloads
Home   >   mailing lists

Re: Dynamic SQL - transition from ms to pg - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Dynamic SQL - transition from ms to pg
Date
Msg-id CAFj8pRCRu6z4D0e4=c2_A_it0HDeDu5fROAUo=eyjNukBLN0Mg@mail.gmail.com
Whole thread Raw
In response to Dynamic SQL - transition from ms to pg  (Erik Darling <edarling80@gmail.com>)
Responses Re: Dynamic SQL - transition from ms to pg  (Erik Darling <edarling80@gmail.com>)
List pgsql-general
Tree view
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

Thanks,
Erik


pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Dynamic SQL - transition from ms to pg
Next
From: Martijn van Oosterhout
Date:
Subject: WAL archive cleanup om master