You have an unresolved naming conflict.
You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting.
Here you declare a variable named measurement_id
:
DECLARE
measurement_id INTEGER;
It's a folly to use ambiguous variable names to begin with. If you do it anyway, you must know what you are doing. I make it a habbit to prepend variable names with an underscore unlike column names, like _measurement_id
.
The later SELECT
statement is ambiguous:
ORDER BY measurement_id
This would raise an error message in modern PostgreSQL with default configuration. Per the documentation:
By default, PL/pgSQL will report an error if a name in a SQL statement
could refer to either a variable or a table column.
And:
To change this behavior on a system-wide basis, set the configuration
parameter plpgsql.variable_conflict
to one of error, use_variable, or
use_column (where error is the factory default). This parameter
affects subsequent compilations of statements in PL/pgSQL functions,
but not statements already compiled in the current session. Because
changing this setting can cause unexpected changes in the behavior of
PL/pgSQL functions, it can only be changed by a superuser.
In Postgres older than 9.0 this would be resolved to mean the variable. Per the documentation
In such cases you can specify that PL/pgSQL should resolve ambiguous
references as the variable (which is compatible with PL/pgSQL's
behavior before PostgreSQL 9.0)
Bold emphasis mine.
This would result in arbitrary results, since the sort order is now undetermined.
Audited Function
CREATE OR REPLACE FUNCTION insaft_function()
RETURNS TRIGGER AS
$func$
DECLARE
_measurement_id integer;
_file_header_index_start integer := TG_ARGV[0]::int;
_file_header_index_end integer := TG_ARGV[1]::int;
BEGIN
SELECT a.measurement_id INTO _measurement_id
FROM measurements a
ORDER BY a.measurement_id DESC -- you had ambiguity here!
LIMIT 1;
IF TG_OP = 'INSERT' THEN -- noise if only used in AFTER INSERT trigger
INSERT INTO file_headers (measurement_id, file_header_index_start
, file_header_index_end)
VALUES (_measurement_id, _file_header_index_start, _file_header_index_end);
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;
Note how I named it insaft_function()
, since this is only to be used in an AFTER INSERT
trigger.
Trigger:
CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function(1, 666);
But for the provided setup, you can radically simplify the function:
CREATE OR REPLACE FUNCTION insaft_function()
RETURNS TRIGGER AS
$func$
BEGIN
INSERT INTO file_headers (measurement_id, file_header_index_start
, file_header_index_end)
VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);
RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;
If you don't want to write your own parser, the quick and dirty solution would be to replace <
and >
with some other punctuation, so that the existing parsers don't decide to discard them as html tags.
SELECT *
FROM xmldocument
WHERE to_tsvector(regexp_replace(content::text,'[<>]',' ','g')) @@ 'boo2';
As to why I don't just use ILIKE % etc. the reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.
If you use the pg_trgm
extension, you can build a gin index on VARCHAR values which will optimize ILIKE queries. How effective it is depends on the size of your indexed documents, and the size of your query. I'd recommend trying it and seeing how it does for you.
Version 1.2 of pg_trgm (to be included in PostgreSQL 9.6, but it is fairly easy to back-port into 9.4 and 9.5 if you are willing to compile some code) will be much more effective with large queries.
Best Answer
The
UNION
result set data types will be determined by logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without explicit type specification, string literals are treated as being of typeunknown
.Specifically, these rules apply in your case:
This basically means that if you have at least one numeric literal value in a particular (in your case fourth) column in your
UNION
ised query, Postgres will attempt to coerce the values in the same column in otherSELECT
s into an number, which obviously fails for the character value'2017-01-01'
.Subsequently, if you have at least one character value in that column that cannot be cast into a number, you will have to use explicit character values for the fourth column in all
SELECT
s:H/T to Andriy M for his comment that prompted me to read the manual more thoroughly.