topic Re: Libname ... postgres ... defer=yes: Process that establishes connection in SAS Programming

Libname ... postgres ... defer=yes: Process that establishes connection

Patrick — Wed, 12 Jul 2023 10:19:58 GMT

Hi all,

I'm looking for an approach as simple as possible that forces SAS to establish a connection to postgres for a pre-assigned library defined in metadata with option defer=yes set.

Problem

If I run below code then the query returns zero rows and macro variable &pg_schema is blank. It appears the library doesn't appear in the dictionary table prior to the database connection being established.

libname mydb POSTGRES ... DEFER=YES;
%let pg_schema=;
proc sql noprint;
  select sysvalue into :pg_schema trimmed 
  from dictionary.libnames
  where libname="MYDB" and sysname='Schema/Owner' 
  ;
quit;
%put &=pg_schema;

If I "do" something that accesses the database then the connection gets established and the macro variable gets populated. In below code the "do" is Proc Contents

libname mydb POSTGRES ... DEFER=YES;

proc contents data=mydb._all_;quit;

%let pg_schema=;
proc sql noprint;
  select sysvalue into :pg_schema trimmed 
  from dictionary.libnames
  where libname="MYDB" and sysname='Schema/Owner' 
  ;
quit;
%put &=pg_schema;

The "funny" thing: If I run the first version of my code out of SAS EG then things work and the macro variable gets populated.

My code needs to work when running under SAS IRM (SAS Infrastructure for Risk Management) under SAS9.4 M7/RHEL. IRM does process orchestration.

Under IRM the first code version without Proc Contents returns zero rows, the version with Proc Contents works.

What I'm looking for

Something simpler than Proc Contents to establish the connection. There can be quite a few tables under a schema. Assume that I don't know any table names in advance.

...or may-be I just do: options obs=1;proc contents ...; options obs=max

Any other simple syntax ideas welcome. Ideally something that doesn't create output.

Re: Libname ... postgres ... defer=yes: Process that establishes connection

FreelanceReinh — Wed, 12 Jul 2023 13:31:41 GMT

Hi @Patrick,

My IT environment is totally different, but maybe one of the five suggestions below is helpful anyway ...

@Patrick wrote:

Ideally something that doesn't create output.

The NOPRINT option could suppress the PROC CONTENTS output.
Have you tried any of these functions?
```
data _null_;
if libref('mydb');
run;
```

data _null_;
rc=exist('mydb.somename');
run;

data _null_;
rc=rename('mydb.nonex', 'nonex');
run;

Sometimes using the SASHELP views (rather than the DICTIONARY tables) makes a difference.

data _null_;
set sashelp.vlibnam;
where libname="MYDB" and sysname='Schema/Owner';
call symputx('pg_schema',sysvalue);
run;

Re: Libname ... postgres ... defer=yes: Process that establishes connection

Patrick — Mon, 17 Jul 2023 10:26:23 GMT

@FreelanceReinh

Thank you for all these proposals. I've done quite a few trials but in my environment and under the assumption that I don't know a table name in advance the only two approaches that I could identify working would either be a proc contents data=<libref>._all_ or then to query SAS metadata to retrieve the schema name defined with the library definition.

In real life: I've taken a step back and decided that if a developer knows the libref then it's not too much to ask to also figure out the schema name. I've created now a parameter to my macro for the schema name and don't try to retrieve it anymore. And I've set the default of the schema name to what it needs to be in potentially all the cases where this macro gets used.

I went initially for something dynamic because the installers at my site managed to call things sometimes different in different environments - luckily the schema names are the same in all environments so one can develop code that doesn't change when pushing it into higher environments.