Hello list,
I am creating a plpgsql procedure in Postgres 9.4 (also testing in 9.3.6) to move all of the tables that are not in a default tablespace (pg_default, pg_global, or 0) into the tablespace pg_default. However when it executes I get an error 'ERROR: invalid input syntax for type oid:' which I do not know how to resolve..
The procedure executes the following select query, which returns the relname (tablename, type name) and nspname (schema name, type name) of each table that are not in the default tablespaces, into a variable called row_data (of type pg_catalog.pg_class%ROWTYPE):
SELECT pg_class.relname, pg_namespace.nspname
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
WHERE pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_default')
AND pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_global')
AND pg_class.reltablespace<>0
AND pg_class.relkind='r'
ORDER BY pg_class.relname;
Using the example database EDBSTORE (example database provided by Enterprise DB) the query returned the table 'inventory' which was in schema 'edbstore' (which I had stored on tablespace 'edbstore', not pg_default):
relname | nspname
-----------+----------
inventory | edbstore
(1 row)
The procedure loops through each returned row and executes an ALTER TABLE command to move them to the tablespace pg_default:
EXECUTE 'ALTER TABLE '|| row_data.nspname || '.' || row_data.relname ||' SET TABLESPACE pg_default';
(so in the above edbstore example it should execute "ALTER TABLE edbstore.inventory SET TABLESPACE pg_default;")
However, when I run the procedure it is returning the following error:
ERROR: invalid input syntax for type oid: "edbstore"
CONTEXT: PL/pgSQL function move_table_tablespaces_to_pg_default() line 18 at FOR over SELECT rows
Does anyone understand this error?
The full plpgsql function is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE FUNCTION move_table_tablespaces_to_pg_default () RETURNS INTEGER AS $$
-- Loops through the tables not in the tablespace pg_default, pg_global, or the default tablespace and moves them to the pg_default tablespace
-- Returns the number of tables that were moved
DECLARE
-- Declare a variable to hold the counter of tables moved
objects_affected INTEGER = 0;
-- Declare a variable to hold rows from the pg_class table
row_data pg_catalog.pg_class%ROWTYPE;
BEGIN
-- Iterate through the results of a query which lists all of the tables not in the tablespace pg_default, pg_global, or the default tablespace
FOR row_data IN (SELECT pg_class.relname, pg_namespace.nspname FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
WHERE pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE spcname='pg_default') AND pg_class.reltablespace<>(SELECT oid FROM pg_tablespace WHERE
spcname='pg_global') AND pg_class.reltablespace<>0 AND pg_class.relkind='r' ORDER BY pg_class.relname) LOOP
-- execute ALTER TABLE statement on that table to move it to tablespace pg_default
EXECUTE 'ALTER TABLE '|| row_data.nspname || '.' || row_data.relname ||' SET TABLESPACE pg_default';
-- increment count of tables moved
objects_affected := objects_affected + 1;
END LOOP;
-- Return count of tables moved
-- RETURN objects_affected;
END;
$$ LANGUAGE 'plpgsql';
Will
Will J Dunn
