Implementing a function written for Postgres 9.4 in Postgres 8.4

Question

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

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 ;) — András Váczi
– András Váczi, Commented Jul 10, 2015 at 10:51
Sorry, fixed that there! Unfortunately upgrading is not an option. I'll try your suggestion now. — Dan
– Dan, Commented Jul 10, 2015 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
– Dan, Commented Jul 10, 2015 at 11:14

András Váczi · Accepted Answer · 2015-07-10 15:37:01Z

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.

edited Jul 10, 2015 at 15:37

answered Jul 10, 2015 at 12:22

András Váczi

31.8k13 gold badges103 silver badges152 bronze badges

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
– Dan

2015-07-10 13:26:08 +00:00
Commented Jul 10, 2015 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)

András Váczi
– András Váczi

2015-07-10 13:34:34 +00:00
Commented Jul 10, 2015 at 13:34
Thanks once again. I wish I could use up to date versions!

Dan
– Dan

2015-07-10 13:46:25 +00:00
Commented Jul 10, 2015 at 13:46

Add a comment |

Stack Exchange Network

Implementing a function written for Postgres 9.4 in Postgres 8.4

1 Answer 1

Your Answer

Hot Network Questions

Implementing a function written for Postgres 9.4 in Postgres 8.4

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions