Postgres - LiveStone/knowledge-base GitHub Wiki

1. Problem with dollar-quoted-in-postgresql

Problem: JDBC driver can't correctly interpret plpgsql separator $$.
Solution: Use separate file for each plpgsql block.

Solution Details

Liquibase changelog file content: 

<changeSet id="1" author="andrey" dbms="postgresql" >
    <sqlFile path="permissions_view_script/refresh_permissions_view.sql" relativeToChangelogFile="true" splitStatements="false"/>
</changeSet>

refresh_permissions_view.sql

CREATE OR REPLACE FUNCTION refresh_permissions_view(force_refresh boolean default false)
    RETURNS VOID AS $body$
BEGIN
    IF (EXISTS (SELECT 1 FROM permissions_view_refresh_log WHERE refreshed = false) OR force_refresh) THEN
        REFRESH MATERIALIZED VIEW mv_users_permissions;
        UPDATE permissions_view_refresh_log SET refreshed = true WHERE refreshed = false;
    END IF;
END
$body$
LANGUAGE plpgsql;

2. Execute custom sql block

Block example 
DO $$
DECLARE
    tables varchar[] := ARRAY['t1','t2'];
    t varchar;
BEGIN
    FOREACH t IN ARRAY tables
    LOOP
        EXECUTE format('DROP TABLE %s', t);
    END LOOP;
END
$$;
⚠️ **GitHub.com Fallback** ⚠️