Executing Create Function on Aurora Serverless instance of Postgres fails

Question

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:

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?

CerealPort · Accepted Answer · 2019-12-15 19:27:11Z

1

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\$;"

answered Dec 15, 2019 at 19:27

CerealPort

335 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Myz Over a year ago

For someone who is trying to make it work through AWS Query Editor or Windows (10) PowerShell or CMD, this solution will not work. I even tried to reproduce the same function as OP but it failed. I tried different solutions to escape $ but it didn't work. Finally, I was able to do it via Ubuntu using solution posted here stackoverflow.com/questions/57994804/… P.S. Not sure if this is the best solution but please correct me if I am wrong.

the_smart_home_maker Over a year ago

great comment - I was not aware of the fact that you need to escape the '$' character. I was already going crazy of RDS editor not taking my statement. Thank you!

Collectives™ on Stack Overflow

Executing Create Function on Aurora Serverless instance of Postgres fails

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related