Reputation: 33
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() lineX
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
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