Re: PL\pgSQL 'ERROR: invalid input syntax for type oid:' [PostgreSQL 9.3.6 and 9.4]

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(dot)klaver(at)aklaver(dot)com