Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Babelfish for Aurora PostgreSQL procedure reference

Overview

You can use the following procedure for Amazon RDS DB instances running Babelfish for Aurora PostgreSQL for a better query performance:

sp_babelfish_volatility

PostgreSQL function volatility helps the optimizer for a better query execution which when used in parts of certain clauses has a significant impact on query performance.

Syntax

sp_babelfish_volatility ‘function_name’, ‘volatility’

Arguments

Result set

If the parameters are not mentioned then the result set is displayed under the following columns: schemaname, functionname, volatility.

Usage notes

PostgreSQL function volatility helps the optimizer for a better query execution which when used in parts of certain clauses has a significant impact on query performance.

Examples

The following examples shows how to create simple functions and later explains how to use sp_babelfish_volatility on these functions using different methods.

1> create function f1() returns int as begin return 0 end
2> go

1> create schema test_schema
2> go

1> create function test_schema.f1() returns int as begin return 0 end
2> go

The following example displays volatility of the functions:

1> exec sp_babelfish_volatility
2> go
            
schemaname  functionname volatility
----------- ------------ ----------
dbo         f1           volatile
test_schema f1           volatile                
            

The following example shows how to change the volatility of the functions:

1> exec sp_babelfish_volatility 'f1','stable'
2> go
1> exec sp_babelfish_volatility 'test_schema.f1','immutable'
2> go
            

When you specify only the function_name, it displays the schema name, function name and volatility of that function. The following example displays volatility of functions after changing the values:

1> exec sp_babelfish_volatility 'test_schema.f1'
2> go
            
schemaname  functionname volatility
----------- ------------ ----------
test_schema f1           immutable             
            

1> exec sp_babelfish_volatility 'f1'
2> go
            
schemaname  functionname volatility
----------- ------------ ----------
dbo         f1           stable            
            

When you don't specify any argument, it displays a list of functions (schema name, function name, volatility of the functions) present in the current database:

1> exec sp_babelfish_volatility 
2> go
            
schemaname  functionname volatility
----------- ------------ ----------
dbo         f1           stable
test_schema f1           immutable   
            

sp_execute_postgresql

You can execute PostgreSQL statements from T-SQL endpoint. This simplifies your applications as you don’t need to exit T-SQL port to execute these statements.

Syntax

sp_execute_postgresql [ @stmt = ] statement

Arguments

1>exec sp_execute_postgresql 'create extension pg_stat_statements; drop extension pg_stat_statements'
2>go

Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
expected 1 statement but got 2 statements after parsing

Usage notes

CREATE EXTENSION

Creates and loads a new extension into the current database.

1>EXEC sp_execute_postgresql 'create extension [ IF NOT EXISTS ] <extension name> [ WITH ] [SCHEMA schema_name] [VERSION version]';
2>go
            

The following example shows how to create an extension:

1>EXEC sp_execute_postgresql 'create extension pg_stat_statements with schema sys version "1.10"';
2>go

Use the following command to access extension objects:

1>select * from pg_stat_statements;
2>go                    
                

1>select * from [public].pg_stat_statements;
2>go

ALTER EXTENSION

You can upgrade to a new extension version using ALTER extension.

1>EXEC sp_execute_postgresql 'alter extension <extension name> UPDATE TO <new_version>';
2>go

DROP EXTENSION

Drops the specified extension. You can also use if exists or restrict options to drop the extension.

1>EXEC sp_execute_postgresql 'drop extension <extension name>';
2>go