parsing breaks in TRIGGER

[skariel]

The following schema:

CREATE SCHEMA tdd;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto
    SCHEMA public
    VERSION "1.3";

CREATE FUNCTION tdd.trigger_set_timestamp() RETURNS trigger
LANGUAGE plpgsql
AS $$BEGIN
  NEW.ts_updated = NOW();
  RETURN NEW;
END;
$$;

CREATE TABLE tdd.tests (
    test_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    title text DEFAULT ''::text NOT NULL,
    descr text DEFAULT ''::text NOT NULL,
    ts_created timestamp with time zone DEFAULT now() NOT NULL,
    ts_updated timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TRIGGER set_timestamp BEFORE UPDATE ON tdd.tests FOR EACH ROW EXECUTE FUNCTION tdd.trigger_set_timestamp();

gives the error:

sql/schema/schema.sql:1:1: syntax error at or near "FUNCTION"

Maybe these statements should be ignored as they have no influence on the generated go code

[kyleconroy]

I added the CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; line to your example SQL to make it work for others trying to reproduce the error.

[kyleconroy]

sqlc relies on https://github.com/lfittl/pg_query_go, which currently uses the PostgreSQL 10 parser. The EXECUTE FUNCTION syntax for triggers was introduced in PostgreSQL 11. I've opened up an issue to get pg_query_go updated to PostgreSQL 11 or 12.

In the meantime, you can change FUNCTION to PROCEDURE to fix the error.

[jakelowen]

I can see you are already working on this, but I encountered another example in case it helps.

My migration contains a function starting with CREATE OR REPLACE FUNCTION insert_name(_prefix text, _first text, _middle text, _last text, _suffix text, OUT _name_id BIGINT) AS......

Which throws a SQLC error:

Looking forward to pg_query_go supporting Functions because for my use case Procedures as procedures cannot have OUT arguments

Thanks for an excellent library. I'll figure out a workaround the pg_query_go stuff in the interim.

[nikitaksv]

@kyleconroy Is there any progress on this issue?
I am getting this error:"migrations/init.sql:1:1: syntax error at or near "PROCEDURE""

init.sql

CREATE PROCEDURE ru_fias.sp_fias2searchengine()
    LANGUAGE sql
AS $_$
    .................
$_$;

PostgreSQL
v12.5

sqlc version
v1.6.1-devel

[cbio-mannyschneck]

sqlc relies on https://github.com/lfittl/pg_query_go, which currently uses the PostgreSQL 10 parser. The EXECUTE FUNCTION syntax for triggers was introduced in PostgreSQL 11. I've opened up an issue to get pg_query_go updated to PostgreSQL 11 or 12.

In the meantime, you can change FUNCTION to PROCEDURE to fix the error.

Thank you for this!