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
Last activity: Mar 10, 2021, 10:59 PM