How Do I Create Dynamic SQL in Greenplum Database?

This is the second in a series of “How do I do X in Greenplum database” and today’s topic is How do I create dynamic SQL.

Method 1: psql
The psql program is a command line program that is similar to isql, sqlcmd, or sqlplus. You can use it to dynamically create SQL statements to execute and then pipe it to another instance of psql to execute each statement in a separate transaction.

Example:
In this example, I will create a script dynamically that will analyze each table in the retail schema.

psql -A -t -c "SELECT 'ANALYZE ' || table_schema || '.' || table_name || ';' FROM information_schema.tables WHERE table_schema = 'retail'" | psql -a
ANALYZE retail.d_customers;
ANALYZE
ANALYZE retail.d_payment_methods;
ANALYZE
ANALYZE retail.d_products;
ANALYZE
ANALYZE retail.f_sales;
ANALYZE

psql is executed from the shell with -A (unaligned table output mode), -t (print rows only), and -c (command to execute). The Dynamic SQL is generated and then executed by using a | and psql -a. The -a means that the commands generated by the first psql program are echoed and executed.

This is a nice feature when you need to have each statement executed in a separate transaction.

Method 2: PL/pgSQL
The second way of doing this same task is in PL/pgSQL.

CREATE OR REPLACE FUNCTION retail.fn_analyze() RETURNS void AS
$$

DECLARE
    v_function_name text := 'retail.fn_analyze';
    v_location int;
    v_rec record;

BEGIN
    v_location := 1000;
    FOR v_rec IN SELECT 'analyze ' || table_schema || '.' || table_name AS sql
                 FROM information_schema.tables 
                 WHERE table_schema = 'retail' LOOP

        RAISE INFO '%: %', clock_timestamp(), v_rec.sql;        
        EXECUTE v_rec.sql;

    END LOOP;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION '(%:%:%)', v_function_name, v_location, sqlerrm;
END;

$$
LANGUAGE plpgsql;

And the output of the execution.

psql -c "select retail.fn_analyze()"
INFO:  2012-09-11 11:21:45.580327-05: analyze retail.d_customers
INFO:  2012-09-11 11:21:46.102631-05: analyze retail.d_payment_methods
INFO:  2012-09-11 11:21:46.225238-05: analyze retail.d_products
INFO:  2012-09-11 11:21:46.556235-05: analyze retail.f_sales
 fn_analyze 
------------
 
(1 row)

Note that all of the work done in a function is in a single transaction. If there is a problem and the function fails, all of the work done up to that point will be rolled back. So in the case of analyzing tables, the psql technique is probably a better choice but for other use cases, the function technique is better.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.