Postgres Stempipe Research

 

Check PG foreign server

select 
    srvname as name, 
    srvowner::regrole as owner, 
    fdwname as wrapper, 
    srvoptions as options
from pg_foreign_server
join pg_foreign_data_wrapper w on w.oid = srvfdw;






Here is an example of creating a foreign table with postgres_fdw. First install the extension:


CREATE EXTENSION postgres_fdw;

Then create a foreign server using CREATE SERVER. In this example we wish to connect to a PostgreSQL server on host 192.83.123.89 listening on port 5432. The database to which the connection is made is named foreign_db on the remote server:

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

A user mapping, defined with CREATE USER MAPPING, is needed as well to identify the role that will be used on the remote server:

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

Now it is possible to create a foreign table with CREATE FOREIGN TABLE. In this example we wish to access the table named some_schema.some_table on the remote server. The local name for it will be foreign_table:

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');



SAMPLE AWS FOREIGN TABLE:


-- aws.aws_account definition


-- Drop table


-- DROP FOREIGN TABLE aws.aws_account;


CREATE FOREIGN TABLE aws.aws_account (

account_aliases jsonb NULL,

arn text NULL,

organization_id text NULL,

organization_arn text NULL,

organization_feature_set text NULL,

organization_master_account_arn text NULL,

organization_master_account_email text NULL,

organization_master_account_id text NULL,

organization_available_policy_types jsonb NULL,

title text NULL,

akas jsonb NULL,

"partition" text NULL,

region text NULL,

account_id text NULL,

"_ctx" jsonb NULL

)

SERVER steampipe

OPTIONS (table 'aws_account');


Comments

Popular posts from this blog

Flutter for Single-Page Scrollable Websites with Navigator 2.0

A Data Science Portfolio is More Valuable than a Resume

Better File Storage in Oracle Cloud