Querying Steampipe Postgres FDW

Your Steampipe Postgres FDW adds foreign tables to your Postgres installation. Typically, these tables are prefixed with the plugin name. There is extensive documentation for the plugin in the Steampipe Hub, including sample queries for each table. You can also query the information schema to list the foreign tables that have been added to your schema:

select
  foreign_table_name
from
  information_schema.foreign_tables
where
  foreign_table_schema = 'aws_01'

You can use standard Postgres syntax to query the tables. Note that you will have to qualify the table names with the schema name unless you add the schema to the search path:

select
  instance_id,
  instance_type
  instance_state,
  region,
  account_id
from
  aws_01.aws_ec2_instance

There are many examples in the Steampipe documentation, as well as the Steampipe Hub. These examples all use unqualified table names, so if you want to run them as-is, you'll need to add your schema to your search path:

SELECT set_config('search_path', current_setting('search_path') || ',aws_01', false);
show search_path;

You can now unqualified queries:

select
  instance_id,
  instance_type
  instance_state,
  region,
  account_id
from
  aws_ec2_instance

The search path will persist for the duration of your database session. You can revert to the default search path if you want:

set search_path  to default

Refer to the documentation for more details.