Implementing Postgres 9.4 Functions in Postgres 8.4

postgresql

I have a simple function (tested on Postgres 9.4) that I need to implement on Postgres 8.4. The error I get is that function format does not exist. I've been trying to find resources but 8.4 is 6 years old at this stage and it's hard to find relevant information apart from the docs!

CREATE OR REPLACE FUNCTION create_table()
  RETURNS void AS
$BODY$
DECLARE
    rows RECORD;

BEGIN
    FOR rows IN SELECT column as tablename from table
        LOOP
            EXECUTE format('
               CREATE TABLE IF NOT EXISTS %I (
                date date,
                number integer
               )', rows.tablename);

        END LOOP ;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Here's the error message:

ERROR:  function format(unknown, text) does not exist
LINE 1: SELECT  format('
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any suggestions would be greatly appreciated!

EDIT: I've amended the loop in my function to this:

        LOOP
            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || rows || ' (
                date date,
                number integer
               )', rows.tablename;

        END LOOP ;

However now I'm getting this error on quote_ident():

ERROR:  function quote_ident(record) does not exist

Best Answer

The pre-format solution used to be the good old concatenation, made safe by using quote_ident().

In your case, it would look like

...
    EXECUTE $$ 
        CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ (
             date date,
             number integer
        ) $$;
...

Note that here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.

Another shortcoming of version 8.4 is that the CREATE TABLE IF NOT EXISTS syntax does not exist there - it was introduced with 9.1. You can still do a test for it:

IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname = 'rows.tablename' AND nspname = 'your_schema')
THEN
    -- do the EXECUTE
END IF;

If you have only one schema around, you can omit the join to pg_namespace. pg_class and pg_namespace are part of the system catalogs - you may want to check out what else can be found there.

Related Question