在函数中使用从psql命令行传入的变量 [英] Using variable passed in from psql command line, in function

问题描述

我不知道如何在我的plpgsql函数中访问变量。我在Cygwin下使用了Postgres 9.5。

I can't figure out how to access variables inside my plpgsql function. I'm using postgres 9.5 under Cygwin.

functions.sql

-- this works fine
\echo Recreate = :oktodrop

CREATE OR REPLACE FUNCTION drop_table(TEXT) RETURNS VOID AS
$$
BEGIN
    IF EXISTS ( SELECT * FROM information_schema.tables WHERE table_name = $1 ) THEN
        -- syntax error here:
        IF (:oktodrop == 1 ) THEN
            DROP TABLE $1;
        END IF;
    END IF;
END;
$$
language 'plpgsql';

psql.exe -v oktodrop = 1 -f functions.sql

psql.exe -v oktodrop=1 -f functions.sql

Password:
Recreate = 1
psql:functions.sql:13: ERROR:  syntax error at or near ":"
LINE 5:         IF (:oktodrop == 1 ) THEN
                    ^

推荐答案

也许我简化了您的任务（可以告诉我是否这样），但是为什么不创建这样的函数：

Perhaps I've oversimplified your task (feel free to tell me if that's the case), but why not create the function like this:

CREATE OR REPLACE FUNCTION drop_table(tablename TEXT, oktodrop integer)
RETURNS text AS
$$
DECLARE
  result text;
BEGIN
  IF EXISTS ( SELECT * FROM information_schema.tables WHERE table_name = tablename ) THEN
      IF (oktodrop = 1 ) THEN
        execute 'DROP TABLE ' || tablename;
        result := 'Dropped';
      ELSE
        result := 'Not Okay';
      END IF;
    ELSE
      result := 'No such table';
    END IF;
  return result;
END;
$$
language 'plpgsql';

然后实现为：

select drop_table('foo', 1);

我还应该提醒您，因为您尚未指定 table_schema 字段，很可能目标表存在于另一个模式中，并且实际的drop命令将失败，因为它在默认模式中不存在。

I should also caution that because you have not specified the table_schema field, it's conceivable that your target table exists in another schema, and the actual drop command will fail because it doesn't exist in the default schema.

这篇关于在函数中使用从psql命令行传入的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！