PostgreSql Dialect - Support for CREATE OR REPLACE FUNCTION

Fabien-R commented 8 months ago

SQLDelight Version

2.0.0

SQLDelight Dialect

2.0.0

Describe the Bug

To be able to define a trigger in Postgres we need to have it attached to a function. function postgres documentation

Postgres trigger definition

My simple case is to have a timestamp field to be updated each time a row is updated (organization.sq)

CREATE TABLE IF NOT EXISTS organizations
(
    id          uuid                                   NOT NULL
        CONSTRAINT organizations_pkey PRIMARY KEY,
    name        varchar                                NOT NULL,
    national_id varchar                                NOT NULL,
    country     varchar(50)                            NOT NULL,
    zip_code    varchar(20),
    city        varchar(50),
    address     varchar,
    active      boolean                                NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    CONSTRAINT organizations_country_national_id_key
        UNIQUE (country, national_id)
);

-- TODO Create function not supported ?
CREATE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp_organizations
BEFORE UPDATE ON organizations
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

insertOrganization:
INSERT INTO organizations (id, name, national_id, country, zip_code, city, address, active)
VALUES (gen_random_uuid(),?, ?, ?, ?, ?, ?, ?)
RETURNING *;

Is there any workaround ?

Stacktrace

In the current version it seems impossible to declare a function. We get

Compiling with dialect app.cash.sqldelight.dialects.postgresql.PostgreSqlDialect

S:/Kotlin/kotlin-playground/app/src/main/sqldelight/com/fabien/organizations.sq: (19, 0): <stmt identifier clojure real> expected, got 'CREATE'
19    CREATE
      ^^^^^^

griffio commented 8 months ago

I can only add that:

⛑️ The CREATE FUNCTION* and related grammar for PL/pgSQL is not implemented or supported 🦺 Although CREATE TRIGGER exists in base grammar(sql-psi), the EXECUTE PROCEDURE is not implemented - it should be possible to add this to the PostgreSql grammar

🪱 The only work around, would be that if CREATE TRIGGER...EXECUTE PROCEDURE gets supported the CREATE FUNCTION would have to be run external of SqlDelight...

*https://github.com/cashapp/sqldelight/discussions/3944#discussioncomment-6083612

Fabien-R commented 8 months ago

@griffio

Thanks for you answer. And for pointing #3944 Could be great to have this link to that list in the readme; something like "not yet supported for each dialect". Could prevent lot of questions/answers.

⛑️ The CREATE FUNCTION* and related grammar for PL/pgSQL is not implemented or supported

Do you know if it's a choice to not have The CREATE FUNCTION and won't be implemented. Just to be clear about the outlook :)

🪱 The only work around, would be that if CREATE TRIGGER...EXECUTE PROCEDURE gets supported the CREATE FUNCTION would have to be run external of SqlDelight...

Unfortunately If I use SqlDelight, it's not for using another DB utility. For now I will update the INSERT/UPDATE queries for the timestamps field.

Thank you for your answer and to sqLDelight community. Very small, safe and concise tool :). Very pleasant to play with it.

griffio commented 8 months ago

🎫 I have created a separate issue for CREATE TRIGGER https://github.com/cashapp/sqldelight/issues/4812

❓ As for supporting plpgsql https://www.postgresql.org/docs/current/plpgsql.html - I don't have an estimate or know if it would be implemented - even if the trigger function is treated as a subset first https://www.postgresql.org/docs/current/plpgsql-trigger.html

cashapp / sqldelight