Oracle Input Variable =&name Equivalent in Postgres

Question

I'm trying to run the below command in Postgres as a piece of dynamic SQL that accepts an input variable on the command line. In Oracle I would use ='&TABLE_OWNER' to prompt and accept a table owner for example but I can't find the equivalent in Postgres?

eg.

In Oracle I would do this:

select 'drop table '||owner||'.'||table_name||';' from dba_tables where owner='&TABLE_OWNER';

but what would be the equivalent in Postgres?

select 'drop table '||schemaname||'.'||tablename||';' from pg_tables where schemaname='<INPUT_VARIABLE>';

Actually the syntax "&variable" is not Oracle RDBMS at all. It is what's called a substitution variable and used by various interfacing software (sqlplus,SQL developer, TOAD, most other IDEs). What happens is the interface sees &variable, prompts for a value, and then physically changes the line before submitting to the statement, the RDBMS itself never sees the substitution variable. In fact I sometimes use the exact same format in DBeaver connecting to Postgres. Works just fine. You just have to always keep in mind what piece of software you are talking too. — Belayer
– Belayer, Commented Jan 28, 2021 at 19:31

Laurenz Albe · Accepted Answer · 2021-01-28 17:03:23Z

psql variables are referenced with a colon, like :aname.

\prompt 'gimme the schema name' aname SELECT ... FROM information_schema.tables WHERE table_name = :'aname';

The opening quote has to be after the colon.

Collectives™ on Stack Overflow

Oracle Input Variable =&name Equivalent in Postgres

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related