I want to create trigger for Insert operation, and procedure that prints inserted value.
CREATE TRIGGER added_product_info_trigger
BEFORE INSERT
ON products
EXECUTE PROCEDURE added_product_info();
And my procedure
CREATE OR REPLACE FUNCTION added_product_info()
RETURNS trigger
AS
$$
(Select p.productname, s.companyname from products as p, suppliers as s
where p.supplierid = s.supplierid)
$$ LANGUAGE SQL;
How can I print my inserted value?
Sql functions cannot return trigger. You probably wanted to write a plpgsql function.
A trigger function cannot generate any output (like results of a select query).
You can use raise notice to pass some results to the client program:
create or replace function added_product_info()
returns trigger as $$
declare
company text;
begin
select companyname
from suppliers
where supplierid = new.supplierid
into company;
raise notice 'inserted: "%" supplied by "%"', new.productname, company;
return new;
end;
$$ language plpgsql;
The record new is visible in a trigger function only if the trigger is declared for each row
(default is for each statement when records new and old are not accessible):
create trigger added_product_info_trigger
before insert on products
for each row
execute procedure added_product_info();
If a trigger is before insert for each row it must return new.
Note that the client must be ready to get and process the notice. If you run the query in the standard psql shell program, you ll get:
insert into products values ('some product', 1);
NOTICE: inserted: "some product" supplied by "company"
INSERT 0 1
Read:
raise notice: postgresql.org/docs/current/static/…