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.klaver@aklaver.com