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

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