K
K
kondratev-ad2022-01-28 12:00:10
PostgreSQL
kondratev-ad, 2022-01-28 12:00:10

How to call a function from another schema in PostgeSQL?

Hello. Tell me, how can I call a function from another schema in a SQL function trigger?
We have PosgreSQL, the main public schema and the amqp schema, which was formed when pg_amqp was included .
The public schema has a trigger function for tracking INSERT / UPDATE / DELETE in the table:

Function code
BEGIN
   if (TG_OP = 'INSERT') then
       INSERT INTO table_1_audit_log (
           table_1_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp
       )
       VALUES(
           NEW.id,
           null,
           to_jsonb(NEW),
           'INSERT',
           CURRENT_TIMESTAMP
       );
      
          
       RETURN NEW;
       
   elsif (TG_OP = 'UPDATE') then
       INSERT INTO table_1_audit_log (
           table_1_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp
       )
       VALUES(
           NEW.id,
           to_jsonb(OLD),
           to_jsonb(NEW),
           'UPDATE',
           CURRENT_TIMESTAMP
       );
            
       RETURN NEW;
   elsif (TG_OP = 'DELETE') then
       INSERT INTO table_1_audit_log (
           table_1_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp
       )
       VALUES(
           OLD.id,
           to_jsonb(OLD),
           null,
           'DELETE',
           CURRENT_TIMESTAMP
       );
       
       RETURN OLD;
   end if;
    
END;

I want to call the amqp.publish function from the amqp scheme in this function:
SELECT amqp.publish(1, '', 'queu', 'messange')

But it gives me an error:
ERROR: function amqp.publish(integer, unknown, unknown, unknown) does not exist

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
kondratev-ad, 2022-01-28
@kondratev-ad

Helped Melkij's solution to set the types forcibly

SELECT amqp.publish(1, ''::varchar, 'queu'::varchar, 'messange'::varchar)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question