What is best strategy to create trigger?

[mantoze]

Hello,
What is best strategy to create trigger?
I'm trying to create trigger in same migration file where create table is:

-- This is a sample migration.
create table people(
  id serial primary key,
  first_name varchar not null,
  last_name varchar not null,
  created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp without time zone,
);

CREATE TRIGGER set_update_timestamp
    BEFORE UPDATE 
    ON people
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_set_timestamp();

---- create above / drop below ----

drop table people;

I got error:
ERROR: syntax error at or near ")" (SQLSTATE 42601)
LINE 9: )
^

[jackc]

The problem is not the trigger. The error is because of the comma at the end of the updated_at line.

[mantoze]

Oh yes. Excuse me.
Maybe on the same occasion I would like to ask about environment variables.
Can i use .env file? - "The program environment is available at .env"

I have .env file:
DB_HOST=db2
DB_DATABASE=base
DB_USER=postgres
DB_PASS=secret

And my tern.conf:
[database]
host = {{.env.DB_HOST}}
database = {{.env.DB_DATABASE}}
user = {{.env.DB_USER}}
password = {{.env.DB_PASS}}

I got error:
Unable to connect to PostgreSQL:
failed to connect to host=<no value> user=<no value> database=<no value>: hostname resolving error (lookup : no such host)

[jackc]

tern reads from the actual process environment. If you want a file of environment variables to be available you need to run a program that loads that file into the environment. I use https://direnv.net/

[mantoze]

Thank you.
One more thing.
I think parameter version_table in tern.conf don't work. Shema table always created in public shema with name schema_version.

[jackc]

Fixed in v1.10.1.

[revitteth]

Weirdly I see the same issue as OP, but with no extraneous commas, might just be late but can't seem to run in triggers (I can run them in via a query console):

DROP TRIGGER IF EXISTS hook_jobs_trigger ON hook_jobs;

CREATE TRIGGER hook_jobs_trigger
    AFTER INSERT
    ON hook_jobs
    FOR EACH ROW
EXECUTE PROCEDURE hook_jobs_notify();