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 <http://www.postgresql.org/docs/devel/static/catalog-pg-class.html> *(tablename, type *name*) and *nspname <http://www.postgresql.org/docs/devel/static/catalog-pg-namespace.html> *(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'; Thanks!! Will *Will J Dunn* *willjdunn.com <http://willjdunn.com>*
