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