CODEWITHSUNDEEP
postgresql-11
ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

referencing new Table as new_table not visible to the trigger function Postgresql

I am new in Postgresql so this question may be dumb for you guys! I am tring to use the referenced tables in a trigger function, but some how the function doesn't seem to have access on the referenced alias (new_table) :

CREATE FUNCTION Function_Name()
RETURNS TRIGGER AS
$$
BEGIN
    SELECT tbl.some_field INTO new_table.other_field
    from some_table as tbl;
    return null;
END;
$$ LANGUAGE PLPGSQL;

I am having this error:

"new_table.other_field" is not a known variable

and here is the trigger code:

CREATE TRIGGER Trigger_Name 
AFTER INSERT
ON Table_Name
REFERENCING NEW TABLE AS new_table
FOR EACH ROW
EXECUTE FUNCTION Function_Name();

The function code should be executed first and then the trigger's, so how could the function access the alias that is referenced later in the trigger defenition??

and how to access the referenced table aliases in the function?

Note: In my example I am tring to use the alias, so when I use NEW inplace of new_table the function is created successfully!

Upvotes: 1

Views: 3400

Answers (1)

ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

The problem was that i am tring to set data in the NEW table using the alias name, however to do that i should use the original name NEW and not the referenced alias new_table..

The referenced alias new_table could be used to only get data from, like in FROM CLAUSE, joins and WHERE CLAUSE where one doesn't change the data in the referenced table.

Update:

here is an example of what i did to test that:

create table test_table2(
    id int primary key,
    name varchar(255)
    )

create table test_table(
    id int primary key,
    name varchar(255)
    )

create or replace function F_test_table()
returns trigger as $$
begin
    insert into test_table2(id, name)
    select id, name from new_table;
    return null;
end;
$$ LANGUAGE PLPGSQL;

drop trigger if exists tr_test_table ON test_table;
create trigger tr_test_table
AFTER INSERT
ON test_table
REFERENCING NEW TABLE AS new_table
for each row    ------- row level trigger ---------
EXECUTE FUNCTION F_test_table();


insert into test_table
values(1, '11111')

select * from test_table2

notice that the trigger inserts the data into test_table2

Upvotes: 1

Related Questions