Postgresql dynamic sql syntax error

Question

I have a function that accepts an array of table names and loops through the array to create a trigger for each table. The function compiles and is created with no errors. When I go try to run the function I keep getting the following error and I am not sure what's wrong with my syntax

ERROR:

ERROR:  unterminated quoted string at or near "';
ELSE
    RAISE EXCEPTION ''[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %, %'',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;
INSERT INTO audit.Organization VALUES (audit_row.*);
RETURN null;
END;
"
LINE 53:         audit_row.statement_only = 't';
                                          ^

USAGE:

SELECT audit.temp_create_trigger(ARRAY['Organization'])

Here's the function:

CREATE OR REPLACE FUNCTION audit.temp_create_trigger(table_names character varying[])
RETURNS character varying AS
$BODY$
DECLARE
table_name varchar;
i int;
BEGIN
FOR i in 1..array_upper(table_names, 1) LOOP

EXECUTE format('CREATE OR REPLACE FUNCTION audit.trigger_function_%1$s() RETURNS TRIGGER AS $$
DECLARE
    audit_row audit.%1$s;
    include_values boolean;
    log_diffs boolean;
    h_old hstore;
    h_new hstore;
    excluded_cols text[] = ARRAY[]::text[];
BEGIN
    IF TG_WHEN <> ''AFTER'' THEN
        RAISE EXCEPTION ''audit.trigger_function_%1$s may only run as an AFTER trigger'';
    END IF;

    audit_row = ROW(
        nextval(''audit.%1$s_event_id_seq''), -- event_id
        TG_TABLE_SCHEMA::text,                        -- schema_name
        TG_TABLE_NAME::text,                          -- table_name
        TG_RELID,                                     -- relation OID for much quicker searches
        session_user::text,                           -- session_user_name
        current_timestamp,                            -- action_tstamp_tx
        statement_timestamp(),                        -- action_tstamp_stm
        clock_timestamp(),                            -- action_tstamp_clk
        txid_current(),                               -- transaction ID
        current_setting(''application_name''),          -- client application
        inet_client_addr(),                           -- client_addr
        inet_client_port(),                           -- client_port
        current_query(),                              -- top-level query or queries (if multistatement) from client
        substring(TG_OP,1,1),                         -- action
        NULL, NULL,                                   -- row_data, changed_fields
        ''f''                                           -- statement_only
        );

    IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM ''f''::boolean THEN
        audit_row.client_query = NULL;
    END IF;

    IF TG_ARGV[1] IS NOT NULL THEN
        excluded_cols = TG_ARGV[1]::text[];
    END IF;

    IF (TG_OP = ''UPDATE'' AND TG_LEVEL = ''ROW'') THEN
        audit_row.row_data = hstore(OLD.*) - excluded_cols;
        audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
        IF audit_row.changed_fields = hstore('') THEN
            -- All changed fields are ignored. Skip this update.
            RETURN NULL;
        END IF;
    ELSIF (TG_OP = ''DELETE'' AND TG_LEVEL = ''ROW'') THEN
        audit_row.row_data = hstore(OLD.*) - excluded_cols;
    ELSIF (TG_OP = ''INSERT'' AND TG_LEVEL = ''ROW'') THEN
        audit_row.row_data = hstore(NEW.*) - excluded_cols;
    ELSIF (TG_LEVEL = ''STATEMENT'' AND TG_OP IN (''INSERT'',''UPDATE'',''DELETE'',''TRUNCATE'')) THEN
        audit_row.statement_only = ''t'';
    ELSE
        RAISE EXCEPTION ''''[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %%, %%'''',TG_OP, TG_LEVEL;
        RETURN NULL;
    END IF;
    INSERT INTO audit.%1$s VALUES (audit_row.*);
RETURN null;
END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION audit.trigger_function_%1$s()
OWNER TO postgres;', table_names[i]);

EXECUTE format('CREATE TRIGGER audit.%1$s_trigg
BEFORE INSERT OR UPDATE
ON audit.%1$s
FOR EACH ROW
EXECUTE PROCEDURE audit.trigger_function_%1$s();', table_names[i]);

END LOOP;

RETURN 'SUCCESS';
END;
$BODY$
LANGUAGE plpgsql
COST 100;
ALTER FUNCTION audit.temp_create_trigger(character varying[])
OWNER TO postgres;

'...RAISE EXCEPTION ''''[audit.if_modified_func]...' will try to run RAISE EXCEPTION ''[audit.if_modified_func]..., which is not valid (there are more quotes there than needed). It would be somewhat cleaner, if you used dollar-quoting there too. -- Sidenote: you could create a single trigger function, there is no real need to create a function for each table separately. You even use the TG_TABLE_SCHEMA and TG_TABLE_NAME special trigger variables inside, which is needed for that. — pozs
– pozs, Commented Mar 13, 2017 at 10:02
@pozs im trying to use your suggestion but I am having problems. can you look at my new post (stackoverflow.com/questions/43105974/…) — iambdot
– iambdot, Commented Mar 29, 2017 at 23:33

Laurenz Albe · Accepted Answer · 2017-03-13 10:52:06Z

0

There are missing and superfluous quotes :

--IF audit_row.changed_fields = hstore('') THEN  -- missing quotes
IF audit_row.changed_fields = hstore('''') THEN

.....

--RAISE EXCEPTION ''''[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %%, %%'''',TG_OP, TG_LEVEL; --more quotes then needed

RAISE EXCEPTION ''[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %%, %%'',TG_OP, TG_LEVEL;

edited Mar 13, 2017 at 10:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Mar 13, 2017 at 10:30

light souls

7381 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roman Tkachuk Over a year ago

It is a good idea to use $something$ to quote at least functions code

Collectives™ on Stack Overflow

Postgresql dynamic sql syntax error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related