Create a temp table in Postgres DB

[PeteSAS]

I'm struggling with the syntax of creating a temp table in Postgres, as I do not have rights to create a permanent table. Here's what I tried: 

libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>; 

Libname works fine, and I'm able to query tables. 

Temp table creation is where I get stuck: 

proc sql;
create table mypg.'#pathway_set_tmp'n (pathwayid int);

Error received: 

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: syntax error at or near "#"; Error while executing
the query.

Thanks in advance!

[r_behata]

Have you tried the SQL Pass-through facility ?

proc sql noerrorstop;
 connect to postgres as x1(server=mysrv1 port=5432 
    user=mysur1 password='mypwd1' database=mydb1);

 select * from connection to x1 (<Your P-SQL Query Here>);

 disconnect from x1;
 quit;

[PeteSAS]

Thanks for the reply, I played around with that a little along the way - is there a way to specify the schema, and use windows authentication so I don't have to hard-code my user/pwd? 

[HB]

Perhaps create the table in SAS and then export when you are done.

[PeteSAS]

Thanks, could you be a little more specific in regard to exporting the table to Postgres as a temp table? Example would be much appreciated.

[SASKiwi]

This link shows you how to create Postgres temporary tables: https://www.postgresqltutorial.com/postgresql-temporary-table/

If you use SQL Passthru and the Postgres examples in the link it should work. Something like this?

libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>; 

proc sql noprint;
  connect using mypg;
execute (CREATE TEMPORARY TABLE temp_table_name(MyTempTable);) by mypg;
quit;