Sample Header Ad - 728x90

Error when passing variable to function

0 votes
2 answers
202 views
I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function: CREATE OR REPLACE FUNCTION public.intercept_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $function$ declare _qry text; BEGIN if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then SELECT current_query() into _qry; PERFORM pglogical.replicate_ddl_command('_qry', '{default}'), _qry; end if; END; $function$ ; When I tried it, it threw an error: > ERROR: syntax error at or near "_qry" > LINE 1: SELECT pglogical.replicate_ddl_command('_qry', '{default}') > ^ > QUERY: SELECT pglogical.replicate_ddl_command('_qry', '{default}') > CONTEXT: during execution of queued SQL statement: _qry > PL/pgSQL function intercept_ddl() line 6 at PERFORM How to pass _qry variable so we can use it replicate_ddl_command()? --- After fixing single quotes as instructed by Laurenz, I tried again with the command: alter table test alter COLUMN description type text; And got another error message: > ERROR: cannot drop active portal "pglogical" > CONTEXT: during execution of queued SQL statement: alter table test alter COLUMN description type text; SQL statement "SELECT pglogical.replicate_ddl_command( _qry, '{default}')" PL/pgSQL function intercept_ddl() line 7 at PERFORM
Asked by Hendra Budiawan (1 rep)
Mar 10, 2021, 09:26 AM
Last activity: Mar 10, 2021, 10:59 PM