CODEWITHSUNDEEP
sqlpostgresqltriggers
ouroboros
ouroboros

Reputation: 33

Postgresql (PLPGSQL) trigger assignment issue

Given the following schema for a postgresql 9.5 database:

      --
      -- DOMAINS
      -- 
      CREATE DOMAIN data_sources_url_domain AS text NOT NULL;
      CREATE OR REPLACE FUNCTION validates_format_of_url(url text) RETURNS bool AS $$
        BEGIN
          RETURN url ~* '^https?://';
        END;
      $$ LANGUAGE plpgsql;
      ALTER DOMAIN data_sources_url_domain ADD CONSTRAINT format_validation CHECK (validates_format_of_url(value));

      --
      -- TABLES
      --
      CREATE TABLE data_sources (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(),
        url data_sources_url_domain
      );

      --
      -- TRIGGERS & FUNCTIONS
      --
      CREATE OR REPLACE FUNCTION format_data_source_url() RETURNS trigger AS $format_data_source_url$
        DECLARE
          up_to_querystring text;
          querystring_to_end text;
          querystring_minus_fragment text;
        BEGIN
          up_to_querystring := split_part(NEW.url, '?', 1);

          IF length(up_to_querystring) = length(NEW.url) THEN
            NEW.url := lower(up_to_querystring);
            RETURN NEW;
          END IF;

          querystring_to_end := split_part(NEW.url, '?', 2);
          querystring_minus_fragment := split_part(querystring_to_end, '#', 1);

          NEW.url := lower(up_to_querystring) || '?' || querystring_minus_fragment;
          RETURN NEW;
        END;
      $format_data_source_url$ LANGUAGE plpgsql;

      CREATE TRIGGER format_data_source_url BEFORE INSERT OR UPDATE ON data_sources
      FOR EACH ROW EXECUTE PROCEDURE format_data_source_url();

BOTH the following SQL statements:

INSERT INTO data_sources (url) VALUES ('HtTpS://www.example.com/things?foo=bar#fragment');
INSERT INTO data_sources (url) VALUES ('HtTpS://www.example.com/things');

Produce the following error:

ERROR: SPI_connect failed: SPI_ERROR_CONNECT
CONTEXT: PL/pgSQL function format_data_source_url() line X at assignment

With line X being either of the following lines:

10: NEW.url := lower(up_to_querystring);
17: NEW.url := lower(up_to_querystring) || '?' || querystring_minus_fragment;

Any ideas on what is causing this?

The only documentation I can really find about SPI is here, and it seems to be for triggers written in C; this trigger is in PL/PGSQL.

Thanks in advance.

Upvotes: 3

Views: 250

Answers (1)

fl0cke
fl0cke

Reputation: 2884

You need to explicitly cast to data_sources_url_domain before assigning to NEW.url.

  CREATE OR REPLACE FUNCTION format_data_source_url() RETURNS trigger AS $format_data_source_url$
    DECLARE
      up_to_querystring text;
      querystring_to_end text;
      querystring_minus_fragment text;
    BEGIN
      up_to_querystring := split_part(NEW.url, '?', 1);

      IF length(up_to_querystring) = length(NEW.url) THEN
        NEW.url := lower(up_to_querystring)::data_sources_url_domain;
        RETURN NEW;
      END IF;

      querystring_to_end := split_part(NEW.url, '?', 2);
      querystring_minus_fragment := split_part(querystring_to_end, '#', 1);

      NEW.url := (lower(up_to_querystring) || '?' || querystring_minus_fragment)::data_sources_url_domain;
      RETURN NEW;
    END;
  $format_data_source_url$ LANGUAGE plpgsql;

The error is weird though.

Upvotes: 1

Related Questions