列“"具有不受支持的类型"information_schema.sql_identifier"； [英] Column '' has unsupported type "information_schema.sql_identifier"

问题描述

我正在尝试在MySQL Workbench/j中测试我的存储过程.尝试调用存储过程时出现错误.

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 ableok 
(
  name VARCHAR(50) ENCODE lzo
 );

这是我的存储过程:

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

这是我在SQL Workbench/j中调用存储过程的方式:

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

call sp_getdistschema();

结果:

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.

推荐答案

SELECT ... INTO结构用于将查询结果存储到变量中.看来您实际上只是在尝试直接填充distTable.尝试以下方法:

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:

更新:在Redshift/PostgreSQL中处理信息模式时，您显然需要使用CAST转换列数据类型:

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;

这篇关于列“"具有不受支持的类型"information_schema.sql_identifier"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！