SET client_min_messages=NOTICE;
CREATE EXTENSION multicorn;
create or replace function create_foreign_server() returns void as $block$
  DECLARE
    current_db varchar;
  BEGIN
    SELECT into current_db current_database();
    EXECUTE $$ 
    CREATE server multicorn_srv foreign data wrapper multicorn options (
        wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw',
        db_url 'postgresql://$$ || current_user || '@localhost/' || current_db || $$'
    );
    $$;
  END;
$block$ language plpgsql;
select create_foreign_server();
 create_foreign_server 
-----------------------
 
(1 row)

create foreign table testalchemy (
  id integer,
  adate date,
  atimestamp timestamp,
  anumeric numeric,
  avarchar varchar
) server multicorn_srv options (
  tablename 'basetable'
);
create table basetable (
  id integer,
  adate date,
  atimestamp timestamp,
  anumeric numeric,
  avarchar varchar
);
insert into basetable (id, adate, atimestamp, anumeric, avarchar) values 
  (1, '1980-01-01', '1980-01-01  11:01:21.132912', 3.4, 'Test'),
  (2, '1990-03-05', '1998-03-02  10:40:18.321023', 12.2, 'Another Test'),
  (3, '1972-01-02', '1972-01-02  16:12:54', 4000, 'another Test'),
  (4, '1922-11-02', '1962-01-02  23:12:54', -3000, NULL);
select * from testalchemy;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
(4 rows)

select id, adate from testalchemy;
 id |   adate    
----+------------
  1 | 01-01-1980
  2 | 03-05-1990
  3 | 01-02-1972
  4 | 11-02-1922
(4 rows)

select * from testalchemy where avarchar is null;
 id |   adate    |        atimestamp        | anumeric | avarchar 
----+------------+--------------------------+----------+----------
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962 |    -3000 | 
(1 row)

select * from testalchemy where avarchar is not null;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(3 rows)

select * from testalchemy where adate > '1970-01-02'::date;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(3 rows)

select * from testalchemy where adate between '1970-01-01' and '1980-01-01';
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(2 rows)

select * from testalchemy where anumeric > 0;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(3 rows)

select * from testalchemy where avarchar not like '%test';
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(3 rows)

select * from testalchemy where avarchar like 'Another%';
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
(1 row)

select * from testalchemy where avarchar ilike 'Another%';
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(2 rows)

select * from testalchemy where avarchar not ilike 'Another%';
 id |   adate    |           atimestamp            | anumeric | avarchar 
----+------------+---------------------------------+----------+----------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
(1 row)

select * from testalchemy where id in (1,2);
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
(2 rows)

select * from testalchemy where id not in (1, 2);
 id |   adate    |        atimestamp        | anumeric |   avarchar   
----+------------+--------------------------+----------+--------------
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972 |     4000 | another Test
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962 |    -3000 | 
(2 rows)

select * from testalchemy order by avarchar;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
(4 rows)

select * from testalchemy order by avarchar desc;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(4 rows)

select * from testalchemy order by avarchar desc nulls first;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
(4 rows)

select * from testalchemy order by avarchar desc nulls last;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
(4 rows)

select * from testalchemy order by avarchar nulls first;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
(4 rows)

select * from testalchemy order by avarchar nulls last;
 id |   adate    |           atimestamp            | anumeric |   avarchar   
----+------------+---------------------------------+----------+--------------
  3 | 01-02-1972 | Sun Jan 02 16:12:54 1972        |     4000 | another Test
  2 | 03-05-1990 | Mon Mar 02 10:40:18.321023 1998 |     12.2 | Another Test
  1 | 01-01-1980 | Tue Jan 01 11:01:21.132912 1980 |      3.4 | Test
  4 | 11-02-1922 | Tue Jan 02 23:12:54 1962        |    -3000 | 
(4 rows)

select count(*) from testalchemy;
 count 
-------
     4
(1 row)

DROP EXTENSION multicorn cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server multicorn_srv
drop cascades to foreign table testalchemy
DROP table basetable;