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&gt;
/(tablename, type /name/) and /nspname
<http://www.postgresql.org/docs/devel/static/catalog-pg-namespace.html&gt;
/(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&gt;*

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/&gt;*

On Thu, Apr 16, 2015 at 4:36 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote: