Re: How to pass arguments in postgres to sql scripts compared when moving to Oracle sql

On Sat, 2024-04-27 at 09:06 -0400, kunwar singh wrote:
> Hello Listers,
> Hope everyone is doing well.
>
> Question: How to pass arguments in postgres to sql scripts. 
> Details: 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> _at_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 the script 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

That is a very simple thing to do:

psql -U scott -v job="'CLERK'" -v sal=800 Password for user scott:
psql (16.2)
Type "help" for help.

scott=> select ename,job,deptno,sal from emp where job=:job and sal>:sal;  ename | job | deptno | sal
--------+-------+--------+------

 ADAMS  | CLERK |     20 | 1100
 JAMES  | CLERK |     30 |  950
 MILLER | CLERK |     10 | 1300

Another question is why do you ask Postgres questions on the Oracle list? PostgreSQL has quite a few lists of their own and I see no rhyme or reason to ask PgSQL questions on the Oracle list. Here is the list of the PgSQL lists:

https://www.postgresql.org/list/

Please refrain from asking non-Oracle related stuff on the Oracle list. I know that some of the locals do know PgSQL very well but the explicit purpose of this list is discussion of the Oracle stuff. No PgSQL, no MySQL, no MS SQL, no DB2, just Oracle. 

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l