postgresql - pgsql sql函数顺序执行

Question

如果我将这两个 Postgres 函数定义保存在两个单独的 .sql 文件中:

CREATE OR REPLACE FUNCTION column_exists(tablename text, colname text) RETURNS boolean AS 
        $BODY$
        DECLARE
            q text;
            field_name text;
            onerow record;
        BEGIN
            q = 'SELECT column_name FROM information_schema.columns WHERE table_name='''||tablename||''' AND table_schema =''public''';
            FOR onerow IN EXECUTE q
                LOOP
                   field_name := onerow.column_name;
                   IF ((field_name = colname)) then
                    RETURN true;
                   END IF;                  
                END LOOP;
            RETURN false;
        END;
    $BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION correct_col_names() RETURNS VOID AS
    $BODY$
        DECLARE
            q boolean;
        BEGIN                       

             -- rename name column to Name
             select column_exists('National_Parks', 'name') as q;
             IF q = TRUE THEN
            alter table "National_Parks"
            rename column name to "Name";
             END IF;    

             -- remance descriptio column to description
             select column_exists('National_Parks', 'descriptio') as q;
             IF q = TRUE THEN
            alter table "Natioanl_Parks"
            rename column descriptio to "Description";
             END IF;    

        END
    $BODY$
LANGUAGE plpgsql

我需要使用什么语法来顺序调用，比如在另一个脚本中？我试过了

select correct_col_names()

这会返回以下错误:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "correct_col_names" line 7 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "correct_col_names" line 7 at SQL statement

TIA。

Answer 1

问题在于您的 SELECT 语句未对数据执行任何操作。你的

select column_exists('National_Parks', 'name') as q;

应该是

select column_exists('National_Parks', 'name') INTO q;

as 只是将该查询的结果别名为“q”，实际上并没有将其放入 q 变量中。

调用函数的语法 (select correct_col_names()) 对于 SQL 是正确的。修复该函数中的两个错误后，它应该可以工作。

但是，如果您在另一个 PL/PGSQL 函数中尝试 select correct_col_names()，您会得到同样的错误，因为 select 语句实际上没有 任何有结果的东西。 perform correct_col_names() 运行时不会出错，因为 PERFORM 是 PL/PGSQL 语法，用于在您不想保存结果时调用某些东西。