0

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>';
sufs2000
  • 23
  • 4
  • 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 Jan 28 '21 at 19:31

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263