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();