如何在 AWS Aurora RDS Postgres 中创建用户定义的函数 [英] How do I create user-defined function in AWS Aurora RDS Postgres

问题描述

(为简单起见格式化问题)

(Formatting questions for simplicity)

我使用的是 AWS RDS Aurora Postgres 10.7(这是适用于我的 us-west-2 区域的最新版本).我在无服务器模式下使用它，因此我在 AWS 控制台中嵌入了查询编辑器来运行我的查询.我需要编写用户定义的函数来执行某些复杂的数据库操作.我在我的本地 Postgres 实例上尝试了它，它运行良好，但是，在 AWS 上我无法创建函数.

I am using AWS RDS Aurora Postgres 10.7 (this is the latest version available for my us-west-2 region). I am using this in a serverless mode and hence I get the Query Editor embedded inside AWS Console to run my queries. I have a requirement of writing user-defined function to perform certain complex database operations. I tried it on my local instance of Postgres and it works fine, however, on AWS I am not able to create a function.

以下导致错误:错误:END"处或附近的语法错误.请注意在return 1"之后添加分号也会导致错误.

The following results in an error: ERROR: syntax error at or near "END". Please note adding a semicolon after 'return 1' also results in to error.

CREATE OR REPLACE function some_function()
  RETURNS integer AS
$BODY$
Begin
    return 1
End
$BODY$
  LANGUAGE 'plpgsql';

然而，以下内容让我创建了该函数，但由于它没有主体，因此无法使用.

whereas, the following lets me create the function but it is unusable as it has no body.

CREATE OR REPLACE function some_function()
  RETURNS integer AS
$BODY$
Begin
End
$BODY$
  LANGUAGE 'plpgsql';

My questions is: Has anyone used AWS RDS Query Editor to create user-defined functions in Aurora Postgres? Is yes, what part of syntax is wrong in the example above.

推荐答案

我们遇到了同样的问题，并与 AWS 取得了联系，他们确认这确实是查询编辑器工具的问题.他们没有关于何时解决问题的预计到达时间.

We run into the same problem and got in touch with AWS, who confirmed it is indeed a problem with the Query Editor tool. They don't have an ETA on when the problem will be fixed.

好消息是这将适用于 psql.这是他们回复电子邮件的片段:

The good news is that this will work with psql. This is a snippet from their reply email:

$ psql -h database-2.cluster-xx.us-west-2.rds.amazonaws.com -d postgres -U postgres
postgres=> CREATE OR REPLACE FUNCTION trigger_set_updated_at() RETURNS TRIGGER AS $$
postgres$> BEGIN  NEW.updated_at = NOW();  
postgres$> RETURN NEW;END;$$ 
postgres-> LANGUAGE plpgsql;
CREATE FUNCTION

有关如何设置的文档:https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html

我们已经使用 数据 API 来与我们的集群通信，因此对我们来说，最简单的解决方案实际上是使用 AWS CLI 和现有的数据库密钥.

We already use the Data API to communicate with our cluster, so for us the simplest solution is actually using the AWS CLI and the existing database secret.

您可以将函数定义放在 function.sql 文件中:

You can put your function definition in a function.sql file:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
    BEGIN
        RETURN i + 1;
    END;
$$ LANGUAGE plpgsql;

然后在数据库上执行:

cat function.sql | xargs -0 aws rds-data execute-statement 
    --resource-arn arn:aws:rds:eu-west-1:xxx:cluster:cluster-name 
    --secret-arn arn:aws:secretsmanager:eu-west-1:xxx:secret:secret-name-xxx 
    --database "database_name" 
    --sql

希望这是有用的，祝你好运！

Hopefully this is useful, good luck!

这篇关于如何在 AWS Aurora RDS Postgres 中创建用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！