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
On Thu, Apr 16, 2015 at 4:36 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
*Will J Dunn*
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
*willjdunn.com <http://willjdunn.com>*
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
