CODEWITHSUNDEEP
postgresqlamazon-web-servicespostgisamazon-auroraaws-aurora-serverless
CerealPort
CerealPort

Reputation: 33

Executing Create Function on Aurora Serverless instance of Postgres fails

I'm trying to enable PostGIS extensions on an Aurora Serverless Postgres 10.7 instance using the instructions provided by AWS (found here) and executing the steps via the Query Editor in the RDS console. Using these same instructions I was able to successfully execute all the commands on a separate, regular Aurora RDS Postgres instance.

In "Step 4: Transfer Ownership of the Objects to the rds_superuser Role" I'm getting this error:

enter image description here

This also fails if you try and execute the command from the AWS cli using:

aws rds-data execute-statement --resource-arn "<<RESOURCE_ARN>>" \
--database "postgres" --secret-arn "<<SECRET_ARN>>" \
--sql "CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;"

How do I execute these functions via either the RDS Query Editor or the AWS cli?

Upvotes: 0

Views: 602

Answers (1)

CerealPort
CerealPort

Reputation: 33

The RDS Query Editor and the AWS cli both require '$' characters to be escaped. Simply escaping all the instances of the '$' with '\$' does the trick.

aws rds-data execute-statement --resource-arn "<<RESOURCE_ARN>>" \
--database "postgres" --secret-arn "<<SECRET_ARN>>" \
--sql "CREATE FUNCTION exec(text) returns text language plpgsql volatile AS \$f\$ BEGIN EXECUTE \$1; RETURN \$1; END; \$f\$;"

Upvotes: 1

Related Questions