Thanks Adrian! Changing the declaration row_data to be of type RECORD (rather than pg_catalog.pg_class%ROWTYPE) resolved the error :)
- Will *Will J Dunn* *willjdunn.com <http://willjdunn.com/>* On Thu, Apr 16, 2015 at 4:36 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 04/16/2015 07:52 AM, William Dunn wrote: > >> 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? >> > > pg_class has a hidden field oid: > > http://www.postgresql.org/docs/9.3/interactive/catalog-pg-class.html > > When you are doing: > > row_data pg_catalog.pg_class%ROWTYPE; > > that is saying you want the whole row type for pg_class: > > > http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES > > You are not supplying the oid or the columns other then relname and > nspname so the error is expected. > > If it where me I would use a RECORD type: > > > http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS > > It will adapt to the columns actually returned. > > >> 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>* >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
