5

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
  • 1
    Well, the format( is missing from your post - otherwise, you have to concatenate and quote with quote_ident(). But the best suggestion would be upgrading ;) – dezso Jul 10 '15 at 10:51
  • Sorry, fixed that there! Unfortunately upgrading is not an option. I'll try your suggestion now. – Dan Jul 10 '15 at 10:55
  • I cannot understand why, but now I'm getting an error saying "ERROR: function quote_ident(record) does not exist"! I've edited my question to include how I've changed the function so far. – Dan Jul 10 '15 at 11:14
  • Just out of curiosity, why cannot you upgrade? – dezso Jul 10 '15 at 12:44
4

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.

  • Thanks a million. I hadn't seen this syntax anywhere. This is now correctly beginning the loop, however now I'm getting an even weirder error than before! [Err] ERROR: syntax error at or near "NOT" LINE 2: CREATE TABLE IF NOT EXISTS – Dan Jul 10 '15 at 13:26
  • According to postgresql.org/docs/8.4/static/sql-createtable.html, the IF NOT EXISTS syntax didn't exist in 8.4 (appeared in 9.1) – dezso Jul 10 '15 at 13:34
  • Thanks once again. I wish I could use up to date versions! – Dan Jul 10 '15 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.