postgresql - 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>';

question from:https://stackoverflow.com/questions/65941672/oracle-input-variable-name-equivalent-in-postgres

Answer 1

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.