Sql plus equivalent in psql

Posted on

Question :

I’m tasked of migrating some code from sqlplus to psql
In oracle, I have a bash script that excutes a stored procedure and based on the result it calls a sqlplus that spools multiple files to dump data from the data base:
I’m so confused in how I might do this in postgres.

an example

column nomreg noprint new_value  _nomregion
column noreg noprint new_value  Numreg
Select nomreg,noreg From    schema.table;
column numvers noprint new_value _numvers
column datvers noprint new_value _datvers
Select numvers, datvers From schema.table;
Column Dat1 noprint new_value EchM
Column Dat2 noprint new_value EchM_1
Select To_char(Datproech,'YYYYMM') Dat1 from schema.table;
Select NVL(To_char(Min(Datproech),'YYYYMM'),'&EchM') Dat1 from schema.table
 where Substr('&EchM',1,4)='1900';
Select To_Char(Add_months(To_Date('&EchM','YYYYMM'),-1),'YYYYMM') Dat2 from Dual;

set linesize 250
set verify off
Spool &1/Mailing_SPP_&NumReg..lst
select distinct a.Numben|| ' ' ||a.CleBen,'|',a.NumNir||' '||a.CleNir,'|',Rpad(a.Civilite,4,' '),'|',Rpad(LTRIM(RTRIM(UPPER(a.NomPre)))
 ||' '|| LTRIM(RTRIM(UPPER(a.Prenom))),40,' '),'|',Rpad(NVL(a.Adr1F,a.Adr1E),32,' '),'|',Rpad(NVL(a.Adr2F,a.Adr2E),32,' '),'|',
 Rpad(NVL(a.Adr3F,a.CodPosE||' '||a.VilE),32,' '),'|',NVL(a.CodPosF,'     '),'|',Rpad(NVL(a.VilF,a.Pays),26,' '),'|','     '
  FROM AMIAN.tben a, AMIAN.tpaie b where a.numben=b.numben and b.perpai=a.dat1pai and
 To_Char(b.datpai,'YYYYMM')='&EchM' order by 1;
Spool off

in the same file

ttitle CE '-----  Contenu of table TABLE  -----' skip
spool &1/tbil_pla 
set linesize  80
clear breaks
clear computes
column DatProech heading 'Monthy paiments'
column MontPaye heading '  Total payments  '
column Montpaye format B999999999999.99
column NbAlloc heading 'Nbre Allocataires'
column NbPai heading 'Nbre Paiements'
column Nbmen heading 'Nbre of months'
column today noprint new_value datejour
ttitle left _nomregion skip 2 -
ttitle left 'Edition  ' datejour skip 1 -
ttitle left 'Application MYAPPLICATION ' _numvers ' de ' _datvers skip 1 -
center 'HEADER HEADER' skip 1 -
center '=============================' skip 2
select Sysdate Today,DatProech,MontPaye,NbAlloc,NbPai,Nbmen from EXPLOIT.TBIL;
ttitle off
column DatProech heading 'monthly paiments'
column MontavaPay heading 'accounts'
column Montavapay format B999999999999.99
column NbavaAlloc heading 'Nbre beneficiaries'
column NbavaPai heading 'Number of payments'
column Nbavamen heading 'Number of months'
select Sysdate Today,DatProech,Montavapay,NbavaAlloc,NbavaPai,Nbavamen from Schema.table;
spool off 

I know that the question is not percise enough, so in total:

1- How can I batch export files in psql, or would it be a better idea to use procedures and
Orafce’s UTL_File?

2- How can I format a report using psql ‘centering text, aligning text, controling spaces between columns’

Answer :

  1. You can export data to a client file using psql‘s copy command.

    To write query output to a file, you can redirect output with the o directive

  2. psql does not offer ways to align and format output, but psql aligns the output pretty well to begin with, so you won’t have as much trouble as with SQL*Plus to get readable output.

    For advanced needs, you can use pset format html or pset format latex to write output in these formats. Then you can use a CSS or include the report in a Latex file for nice formatting.

Leave a Reply

Your email address will not be published. Required fields are marked *