basics.md

Anonymous code block:

DO $$
BEGIN

  raise notice 'hello world';

END; $$;

Anonymous block for error handling:

DO $$
BEGIN

  begin
    raise exception 'no bueno';
  exception
    when others then
      raise notice 'something went wrong';
  end;
  
  begin
    raise exception 'muy mal' using errcode = 'feature_not_supported';
  exception
    when feature_not_supported then
      raise notice 'the feature is not supported';
  end;
  

END; $$;

Function:

CREATE OR REPLACE FUNCTION say_hello(name text)
RETURNS text AS $$
BEGIN

  raise notice 'saying hello to %', name;
  return 'Hello ' || name;

END; $$ LANGUAGE plpgsql;

SELECT say_hello('dave');

notify_trigger.sql

-- trigger code
create or replace function handle_cid_log() returns trigger as $$
declare
begin

    PERFORM pg_notify(TG_TABLE_NAME, to_json(new)::text);
    return null;

end;
$$ language plpgsql;

-- trigger
begin;
  drop trigger if exists handle_cid_log on "cid_log";
  create trigger handle_cid_log
      after insert or update on "cid_log"
      for each row execute procedure handle_cid_log();
commit;

trigger.sql

-- table
create table if not exists messages (
  sender text,
  receiver text,
  msg text
);

-- trigger code
create or replace function log_message()
returns trigger
as $$
declare
begin

  raise notice 'message sent from % to % body %', new.sender, new.receiver, new.msg;
  return null;

end;
$$ language plpgsql;

-- trigger
do $$ begin
  create trigger log_message
  after insert on messages
  for each row execute procedure log_message();
exception 
  when others then null;
end $$;

-- insert
insert into messages values ('steve', 'dave', 'hey there');