Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s0hsX-006fgi-R9 for pgsql-sql@arkaria.postgresql.org; Sat, 27 Apr 2024 13:14:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s0hsU-006f6p-W9 for pgsql-sql@arkaria.postgresql.org; Sat, 27 Apr 2024 13:14:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s0hsU-006f6h-Gz for pgsql-sql@lists.postgresql.org; Sat, 27 Apr 2024 13:14:07 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s0hsR-000Img-W0 for pgsql-sql@lists.postgresql.org; Sat, 27 Apr 2024 13:14:05 +0000 Received: by mail-lf1-x12c.google.com with SMTP id 2adb3069b0e04-518931f8d23so3221048e87.3 for ; Sat, 27 Apr 2024 06:14:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=reddoc.net; s=google; t=1714223642; x=1714828442; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=boeDd/Z4LI8oWie36mpJvDXDkBdxbdJDzF7vP4JKOkg=; b=PwjuU+FmkUCBtTK4DBjH//F4QjGJmMWDNlUiJcyNt3jyJneaL3F+M/6tmAok+Rgn+g N9tHKvQHLuuo+wKIa5VifslRPjYBHHChQsYd9yjcnH/GxtNYIZHN0jqAdFQGK+gl3/fg UnhZpihb+unjGb1XlQPZEOtbSUpb9G3iuOAXQ= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714223642; x=1714828442; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=boeDd/Z4LI8oWie36mpJvDXDkBdxbdJDzF7vP4JKOkg=; b=TnL5GGCh1VvMafp+YVp02ZUkQu+0LA+CNBt7P4bUN9/ey+hWugB3qn5E7vMpS5Ok/1 ZnY97cOrsLpJt3sQmXlwSwX0YFanP9yuGWLPIfJHlysN3EjW7g144Eqi4vd5vXKdRk4W 4NpOzx/vItlDl4guakJt0fM6ZJm2gt7cvJR4JdcKCOTA60NW2lMcQOlYuM/lhN4oVNJ7 Inb0lbOWim0KBxwNtU538mZ3Glnuzf1rElDeEX0mqrpAtirB00A/L80QTDKsUDm/2Emu ctKAwU/3CL2pKXY1LiVKsDtG+uulgMLIE6Pilk22jwDQGLHY7IA95wT65bmM6fLmOMl3 1asQ== X-Gm-Message-State: AOJu0Yzj/Ii9zK/0oJBkYyPImBJn0AmCF8Eimg1SDQDJvhLe8YuLIgzz GGPHyVQnRcWtdets1ShG5GlirdCKE+xXsdc6jnm3Zo/94yYQEQ3GYezT59vQJNmj3jpv+N0IvVI zTWafN4LUpZleIjD/Bf8dcxJxZOFjwD1waxmckg== X-Google-Smtp-Source: AGHT+IH3M2auSwrSlgrmUwnXxQSCoQESgru4o1zMUApt9nvzYFMwXdconLb3wowHBQhcjxSgr6Pogyqv3XNJgJV3cN0= X-Received: by 2002:a05:6512:1086:b0:519:6953:2ffc with SMTP id j6-20020a056512108600b0051969532ffcmr4351710lfg.42.1714223641927; Sat, 27 Apr 2024 06:14:01 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Samed YILDIRIM Date: Sat, 27 Apr 2024 16:13:50 +0300 Message-ID: Subject: Re: How to pass arguments in postgres to sql scripts. To: kunwar singh Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003e4789061713cb75" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003e4789061713cb75 Content-Type: text/plain; charset="UTF-8" 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, 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. > > 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 > --0000000000003e4789061713cb75 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Kunwar,

You can check -v option of psql, 4th paragraph and \set command i= n "Meta-Commads" section, and "Variables" subsection un= der "Advanced Features".



Best regards.
Samed YILDIRIM

On Sat, 27 Apr 2024,= 16:03 kunwar singh, <krishs= ingh.111@gmail.com> wrote:
<= div dir=3D"ltr">
Hi friends, =C2=A0Question: How to pass arguments in p= ostgres 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 arg= uments) =C2=A0which make my monitoring/troubleshooting life simpler. How ca= n 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.

Oracle
=3D=3D=3D=3D=3D=3D
cat appjobcheck= .sql

SELECT
=C2=A0 =C2=A0 SID,
=C2=A0 =C2=A0 SYS_CONTEXT.GET_= CURRENT_SCHEMA() AS "SCHEMA", -- Similar to datname
=C2=A0 =C2= =A0 USERNAME,
=C2=A0 =C2=A0 PROGRAM AS "APPLICATION_NAME",
= =C2=A0 =C2=A0 STATUS,
=C2=A0 =C2=A0 SQL_ID, -- Use V$SQL to get full que= ry text based on SQL_ID
=C2=A0 =C2=A0 LOGON_TIME AS "BACKEND_START&= quot;,
=C2=A0 =C2=A0 SQL_EXEC_START AS "QUERY_START",
FROM<= br>=C2=A0 =C2=A0 V$SESSION
WHERE
=C2=A0 =C2=A0 STATUS =3D 'ACTIVE= ' -- Filter to active sessions
=C2=A0 =C2=A0 AND TYPE !=3D 'BACK= GROUND'; -- Exclude background processes
=C2=A0 =C2=A0 AND program= =3D'&1';

The way to invoke from sqlplus is .

SQL= > @appjobcheck batchprocessapp1 =C2=A0---- batchprocessapp1 is what I wa= nt to monitor


Postgres
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
cat= appjobcheck.sql

SELECT
=C2=A0 =C2=A0 pid,
=C2=A0 =C2=A0 da= tname,
=C2=A0 =C2=A0 usename,
=C2=A0 =C2=A0 application_name,
= =C2=A0 =C2=A0 state,
=C2=A0 =C2=A0 query,
=C2=A0 =C2=A0 backend_star= t,
=C2=A0 =C2=A0 query_start
FROM pg_stat_activity where application_= name=3D'&1';

The way I am trying invoke from postgres is= .


postgres=3D> \i appjobcheck.sql batchprocessapp1 =C2=A0--= -- batchprocessapp1 is what I want to monitor
=C2=A0pid | datname | usen= ame | application_name | state | query | backend_start | query_start
---= --+---------+---------+------------------+-------+-------+---------------+-= ------------
(0 rows)

\i: extra argument "batchprocessapp1&q= uot; ignored

--
Cheers,
Kunwar
--0000000000003e4789061713cb75--