MIME-Version: 1.0
From: kunwar singh <krishsingh.111@gmail.com>
Date: Sat, 27 Apr 2024 09:03:16 -0400
Message-ID: 
 <CAJSrDUqgMx2+bhX0f9MWDT9LyC4E44wFbTiqC+wboHc__QyV_g@mail.gmail.com>
Subject: How to pass arguments in postgres to sql scripts.
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006a3d3d061713a595"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJSrDUqgMx2%2BbhX0f9MWDT9LyC4E44wFbTiqC%2BwboHc__QyV_g%40mail.gmail.com>
Precedence: bulk

--0000000000006a3d3d061713a595
Content-Type: text/plain; charset="UTF-8"

Hi friends,  Question: How to pass arguments in postgres to sql scripts.
Context. When I am monitoring my production Oracle databases I have a lot
of simple sql scripts (which require one or more arguments)  which make my
monitoring/troubleshooting life simpler. How can I achieve the same in
postgres? We are migrating our Oracle databases to Postgres  and I am
modifying my scripts to do the same in Postgres.

Oracle
======
cat appjobcheck.sql

SELECT
    SID,
    SYS_CONTEXT.GET_CURRENT_SCHEMA() AS "SCHEMA", -- Similar to datname
    USERNAME,
    PROGRAM AS "APPLICATION_NAME",
    STATUS,
    SQL_ID, -- Use V$SQL to get full query text based on SQL_ID
    LOGON_TIME AS "BACKEND_START",
    SQL_EXEC_START AS "QUERY_START",
FROM
    V$SESSION
WHERE
    STATUS = 'ACTIVE' -- Filter to active sessions
    AND TYPE != 'BACKGROUND'; -- Exclude background processes
    AND program='&1';

The way to invoke from sqlplus is .

SQL> @appjobcheck batchprocessapp1  ---- batchprocessapp1 is what I want to
monitor


Postgres
==========
cat appjobcheck.sql

SELECT
    pid,
    datname,
    usename,
    application_name,
    state,
    query,
    backend_start,
    query_start
FROM pg_stat_activity where application_name='&1';

The way I am trying invoke from postgres is .


postgres=> \i appjobcheck.sql batchprocessapp1  ---- batchprocessapp1 is
what I want to monitor
 pid | datname | usename | application_name | state | query | backend_start
| query_start
-----+---------+---------+------------------+-------+-------+---------------+-------------
(0 rows)

\i: extra argument "batchprocessapp1" ignored

-- 
Cheers,
Kunwar

--0000000000006a3d3d061713a595
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi friends, =C2=A0Question: How to pass arguments in =
postgres to sql scripts. Context. When I am monitoring my production Oracle=
 databases I have a lot of simple sql scripts (which require one or more ar=
guments) =C2=A0which make my monitoring/troubleshooting life simpler. How c=
an I achieve the same in postgres? We are migrating our Oracle databases to=
 Postgres =C2=A0and I am modifying my scripts to do the same in Postgres.<b=
r></div><div><br></div><div>Oracle <br>=3D=3D=3D=3D=3D=3D<br>cat appjobchec=
k.sql <br><br>SELECT<br>=C2=A0 =C2=A0 SID,<br>=C2=A0 =C2=A0 SYS_CONTEXT.GET=
_CURRENT_SCHEMA() AS &quot;SCHEMA&quot;, -- Similar to datname<br>=C2=A0 =
=C2=A0 USERNAME,<br>=C2=A0 =C2=A0 PROGRAM AS &quot;APPLICATION_NAME&quot;,<=
br>=C2=A0 =C2=A0 STATUS,<br>=C2=A0 =C2=A0 SQL_ID, -- Use V$SQL to get full =
query text based on SQL_ID<br>=C2=A0 =C2=A0 LOGON_TIME AS &quot;BACKEND_STA=
RT&quot;,<br>=C2=A0 =C2=A0 SQL_EXEC_START AS &quot;QUERY_START&quot;,<br>FR=
OM<br>=C2=A0 =C2=A0 V$SESSION<br>WHERE<br>=C2=A0 =C2=A0 STATUS =3D &#39;ACT=
IVE&#39; -- Filter to active sessions<br>=C2=A0 =C2=A0 AND TYPE !=3D &#39;B=
ACKGROUND&#39;; -- Exclude background processes<br>=C2=A0 =C2=A0 AND progra=
m=3D&#39;&amp;1&#39;;<br><br>The way to invoke from sqlplus is . <br><br>SQ=
L&gt; @appjobcheck batchprocessapp1 =C2=A0---- batchprocessapp1 is what I w=
ant to monitor<br><br><br>Postgres <br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>ca=
t appjobcheck.sql <br><br>SELECT <br>=C2=A0 =C2=A0 pid, <br>=C2=A0 =C2=A0 d=
atname, <br>=C2=A0 =C2=A0 usename, <br>=C2=A0 =C2=A0 application_name, <br>=
=C2=A0 =C2=A0 state, <br>=C2=A0 =C2=A0 query,<br>=C2=A0 =C2=A0 backend_star=
t,<br>=C2=A0 =C2=A0 query_start<br>FROM pg_stat_activity where application_=
name=3D&#39;&amp;1&#39;;<br><br>The way I am trying invoke from postgres is=
 . <br><br><br>postgres=3D&gt; \i appjobcheck.sql batchprocessapp1 =C2=A0--=
-- batchprocessapp1 is what I want to monitor<br>=C2=A0pid | datname | usen=
ame | application_name | state | query | backend_start | query_start<br>---=
--+---------+---------+------------------+-------+-------+---------------+-=
------------<br>(0 rows)<br><br>\i: extra argument &quot;batchprocessapp1&q=
uot; ignored<br></div><div><br></div><span class=3D"gmail_signature_prefix"=
>-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D=
"gmail_signature">Cheers,<br>Kunwar</div></div>

--0000000000006a3d3d061713a595--