Postgresql (PLPGSQL) trigger assignment issue

Question

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.

fl0cke · Accepted Answer · 2016-02-10 20:08:23Z

1

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.

answered Feb 10, 2016 at 20:08

fl0cke

2,9041 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ouroboros Over a year ago

Thanks. Do you think, due to the inexact error, this is something that should be shared with the Postgresql Developers?

fl0cke Over a year ago

@ouroboros Yes, it looks like a bug to me. Postgresql 9.4 did not complain about it at all.

Collectives™ on Stack Overflow

Postgresql (PLPGSQL) trigger assignment issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related