This is the third in a series of “How do I do X in Greenplum database” and today’s topic is How do I determine the language for a function.

Inline SQL
A common way to use a function is to transform data directly in the SELECT portion of a query. Here is a quick example.

CREATE TABLE person
(id int NOT NULL,
 fname text,
 lname text,
 company_name text)
DISTRIBUTED BY (id);

--sample data
INSERT INTO person VALUES (1, 'Jon', 'Roberts', null);
INSERT INTO person VALUES (2, null, 'Smith', null);
INSERT INTO person VALUES (3, null, null, 'ABC Company');

Now the use case is to retrieve the “name” of the person but it might be a combination of the three text columns. A business decision was made to do format the name as follows:

SELECT CASE WHEN company_name IS NULL AND fname IS NOT NULL AND lname IS NOT NULL THEN lname || ', ' || fname
            WHEN fname IS NULL AND lname IS NOT NULL THEN lname
            WHEN company_name IS NOT NULL AND fname IS NOT NULL AND lname IS NOT NULL THEN company_name || ': ' || lname || ', ' || fname
            WHEN company_name IS NOT NULL AND fname IS NULL AND lname IS NOT NULL THEN company_name || ': ' || lname 
            WHEN company_name IS NOT NULL AND fname IS NULL AND lname IS  NULL THEN company_name END as name
FROM person;

     name     
--------------
 Roberts, Jon
 ABC Company
 Smith
(3 rows)

It would be much easier and consistent to add this logic to a function to be executed so it centralizes the logic to a single place and makes writing SQL a bit easier. This is when using the language “SQL” is preferred. Here is an example and notice that parameters are referred to $1, $2, $3 rather than the name of the parameter. The SQL language does not support named variables.

CREATE OR REPLACE function fn_get_name(p_fname text, p_lname text, p_company_name text) RETURNS text AS
$$
SELECT CASE WHEN $3 IS NULL AND $1 IS NOT NULL AND $2 IS NOT NULL THEN $2 || ', ' || $1
            WHEN $1 IS NULL AND $2 IS NOT NULL THEN $2
            WHEN $3 IS NOT NULL AND $1 IS NOT NULL AND $2 IS NOT NULL THEN $3 || ': ' || $2 || ', ' || $1
            WHEN $3 IS NOT NULL AND $1 IS NULL AND $2 IS NOT NULL THEN $3 || ': ' || $2 
            WHEN $3 IS NOT NULL AND $1 IS NULL AND $2 IS  NULL THEN $3 END as name
$$
LANGUAGE sql;

And now using this function.

SELECT fn_get_name(fname, lname, company_name) AS name FROM person;
     name     
--------------
 Roberts, Jon
 ABC Company
 Smith
(3 rows)

Transformation
The next use case is when transforming data and PL/pgSQL does a great job at this. You can reference parmeters by name, declare variables, handle errors, etc. The code looks a lot like Oracle PL/SQL too.

CREATE OR REPLACE FUNCTION fn_update_person() RETURNS void AS
$$
DECLARE
    v_rowcount int;
BEGIN
    UPDATE person
    SET fname = initcap(fname),
        lname = initcap(lname),
        company_name = initcap(company_name);

    GET DIAGNOSTICS v_rowcount = ROW_COUNT;

    RAISE INFO '% Rows Updated.', v_rowcount;

END;
$$
LANGUAGE plpgsql VOLATILE;

Notice how I’m not looping through the data or issuing a commit after so many records. There are two reasons for this. First, Greenplum is designed for Big Data so manipulating lots of data in a single transaction is ideal. Secondly, a function in Greenplum is executed in a single transaction. You can’t create a transaction within a function because executing a function is by default in a transaction.

Summary
I hope this helps. When transforming data inline, use the SQL language and use the more robust PL/pgSQL language when transforming data.

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.

This is the first in a series of “How do I do X in Greenplum database” and today’s first topic is How do I get the number of rows Inserted (or Updated/Deleted).

Knowing the number of rows Inserted, Updated, or Deleted is pretty common in database programming.  You may want to key off of this to determine the next step in a procedure or just capture the number of rows in an ETL process.

Sybase and Microsoft SQL Server use a Global Variable to do this which is @@ROWCOUNT.  It is dynamically populated after each statement execution.  In Oracle, you can use SQL%ROWCOUNT to do the same thing.

In Greenplum and in a PL/pgSQL function, you can get the rowcount by using GET DIAGNOSTICS.  Here is an example.

CREATE OR REPLACE FUNCTION fn_update_person() RETURNS void AS
$$
DECLARE
    v_rowcount int;
BEGIN
    UPDATE person
    SET state_code = UPPER(state_code);

    GET DIAGNOSTICS v_rowcount = ROW_COUNT;

    RAISE INFO '% Rows Updated.', v_rowcount;

END;
$$
language plpgsql;

There you go. Pretty simple to get the ROW_COUNT in Greenplum.