Stored Procedure: Column '' has unsupported type "information_schema.sql_identifier"

I am trying to test my stored procedure in MySQL workbench/j. I get an error when I am trying to call the stored procedure.

I have created a table to store the result of my stored procedure

CREATE TABLE IF NOT EXISTS public.distTable 
(
  table_schema VARCHAR(50) ENCODE lzo
 );

This is my stored procedure:

CREATE OR REPLACE PROCEDURE sp_GetDistSchema()
AS '
BEGIN
  SELECT Distinct table_schema INTO public.distTable FROM information_schema.tables;
 END;
 '
LANGUAGE plpgsql;

This is how i call my stored procedure in SQL workbench/j:

call sp_getdistschema();

Result:

An error occurred when executing the SQL command:
call sp_getdistschema()

[Amazon](500310) Invalid operation: Column "table_schema" has unsupported type "information_schema.sql_identifier".; [SQL State=0A000, DB Errorcode=500310]
1 statement failed.

May I know what is the problem?

1 answer

  • answered 2020-09-14 07:27 user9601310

    The SELECT ... INTO structure is used to store a query result into variables. It looks as though you are really just trying to populate the distTable directly. Try this instead:

    Update: When processing the information schema in Redshift/PostgreSQL, you apparently need to convert the column datatypes using CAST:

    CREATE OR REPLACE PROCEDURE sp_GetDistSchema()
    BEGIN
      INSERT INTO distTable SELECT DISTINCT CAST(table_schema AS VARCHAR) FROM information_schema.tables;
    END;