Subject: How to pass arguments in postgres to sql scripts.

From: kunwar singh <[email-masked]>
Date: Sat, 27 Apr 2024 09:03:16 -0400

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

---

From: Samed YILDIRIM <[email-masked]>
Date: Sat, 27 Apr 2024 16:13:50 +0300

Hi Kunwar,

You can check -v option of psql, 4th paragraph and \set command in
"Meta-Commads" section, and "Variables" subsection under "Advanced
Features".

https://www.postgresql.org/docs/16/app-psql.html
https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-VARIABLES

Best regards.
Samed YILDIRIM

On Sat, 27 Apr 2024, 16:03 kunwar singh, <[email-masked]> wrote:

> 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
>  pid | datname | usename | application_name | state | query |
> backend_start | query_start
>

---

From: Feike Steenbergen <[email-masked]>
Date: Fri, 3 May 2024 18:43:42 +0200

On Sat, 27 Apr 2024 at 15:03, kunwar singh <[email-masked]> wrote:
>
> 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.

One way to do it is with --set

$ psql --set var1=postgres -AtXq
postgres=# select pid from pg_stat_activity where usename=:'var1';
185
1939

Kind regards,

Feike Steenbergen