New answers tagged dynamic-sql
DO $$ BEGIN CREATE INDEX "index_name" ON "table" (column); EXCEPTION WHEN DUPLICATE_TABLE THEN -- END $$; No?
What @Craig already explained. Plus, since GRANT can grant privileges on multiple objects at once, you can use a single statement without looping: DO $$ BEGIN EXECUTE ( SELECT 'GRANT ALL ON TABLE ' || string_agg (format('%I.%I', table_schema, table_name), ',') || ' TO test' FROM information_schema.tables WHERE table_schema = ...
GRANT doesn't take wildcards in table identifiers. You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name. If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack ...
First revoke your database REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; then try with your GRANT query.
Top 50 recent answers are included