2

I'm learning PostgreSQL and I'm trying to create a function to ALTER the data types of a given table that has columns with character varying data type.

To achieve this, I've built a cursor that basically queries the information_schema.columns looking for tables that has columns with type character varying to later execute an alter and try to alter the column to text, so that field is not limited by the length of the value.

Here is the function: 

CREATE OR REPLACE FUNCTION my_cursor(db_name TEXT, tbl_schema TEXT, tbl_name TEXT, col_type TEXT) RETURNS void AS $func$
DECLARE
   cid record;
BEGIN
   FOR cid IN SELECT * FROM information_schema.columns AS ic
              WHERE ic.table_catalog=db_name
                AND ic.table_schema=tbl_schema
                AND ic.table_name=tbl_name
                AND ic.data_type=col_type
   LOOP
      EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text', cid.table_schema, cid.table_name, cid.column_name);
   END LOOP;
   RETURN;
END;
$func$ LANGUAGE plpgsql;

SELECT my_cursor('database10232016', 'public', 'continent', 'character varying');

The function compiles and is created successfully, however after running the function I noticed the alter is not running over the table name specified.

Can you help me to identify what went wrong with my cursor?

fndg87
  • 331
  • 4
  • 13

1 Answers1

1

There is nothing wrong with your function per se; it functions just fine. A few points though:

  • The text data type is the same as character varying without a length specified. So basically you are working hard changing nothing, except when there are character varying (n) columns. In the information_schema tables both types are listed as character varying because that is the SQL standard; the text type is a PostgreSQL extension.
  • You are not using a CURSOR, you are using a loop. This is a good thing, avoid cursors when you can because there are usually more efficient ways to do things.
  • In your format() function you should use placeholders instead of string concatenation with ||. Right now you concatenate a string with which you further do no formatting. Instead use format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text', cid.table_schema, cid.table_name, cid.column_name).

If you do have string types of character (n) or character varying (n) and you want to remove the length limit, then you should search for columns where character_maximum_length IS NOT NULL in the same table.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Hi Patrick, Thanks for taking for highlighting these points. However, independently of the datatypes recommendations I'm still struggling to see the changes the ALTER TABLE is intended to do. Can you please try the same function against a local table that has a column of type character varying. The result should be that the character varying columns should turn into Text type columna. – fndg87 Nov 13 '16 at 04:02
  • My point is that the `text` data type will never show for a column. It is just a convenience notation for `character varying`. What you want is to remove the length restriction, that should work with your function but you are better off checking for `character_maximum_length` instead of the column data type. – Patrick Nov 13 '16 at 04:12
  • I understand your point. Now let's say I have columns of type smallint in a table and I want to change them to bigint. Consider the function written to change and try it, it wont work. For instance: SELECT my_cursor('database10232016', 'public', 'continent', 'smallint'); And the Alter part should look like this: format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE bigint', cid.table_schema, cid.table_name, cid.column_name) That's what I'm trying to point. For some reason it is not running the alter function. – fndg87 Nov 13 '16 at 11:59
  • Are you the owner of the table? Do you have the right permissions? Can you run the statement from the command line? – Patrick Nov 13 '16 at 14:06
  • I figured out what my issue was...It was the buggy pgadmin IDE with a death session and it was simulating that working yet. After I ran the command from the from the console it worked alright. Thanks for the support and the advices . – fndg87 Nov 13 '16 at 16:46