Creating a Simple Custom Operator in my Postgres Extension pg_sample_ext
, but when implementing it in the database getting an error. The code for the update script and error are mentioned below.
pg_sample_ext--1.0.1--1.0.2.sql
:-- Create necessary objects for version 1.0.2
-- Custom Operator: @*
-- Description: Custom operator that multiplies two values of type my_type
-- Create a new type to use in our operator.
CREATE TYPE my_type AS (value int);
-- Create a SQL function that defines the behaviour of the custom operator.
-- This function multiplies the values of two my_type operands.
CREATE FUNCTION multiply_values(left my_type, right my_type) RETURNS my_type AS $$
SELECT ROW((left.value * right.value))::int;
$$ LANGUAGE SQL IMMUTABLE;
-- Create a custom operator that multiplies two values of type my_type.
-- The operator symbol is @*.
-- It takes two operands of type my_type and returns a value of the same type.
-- The behaviour is defined by the SQL function multiply_values.
CREATE OPERATOR @* (
PROCEDURE = multiply_values,
LEFTARG = my_type,
RIGHTARG = my_type
);
spartacus=# SELECT ROW(2)::my_type @* ROW(3)::my_type AS result;
ERROR: syntax error at or near "."
LINE 2: SELECT ROW((left.value * right.value))::my_type;
^
QUERY:
SELECT ROW((left.value * right.value))::my_type;
CONTEXT: SQL function "multiply_values" during inlining
The main problem is that right
and left
are reserved SQL keywords, so you cannot use them without double quotes. Avoid such identifiers.
Also, your function has to construct a new my_type
as result:
CREATE FUNCTION multiply_values("left" my_type, "right" my_type) RETURNS my_type
LANGUAGE sql
IMMUTABLE AS
'SELECT ROW("left".value * "right".value)::my_type';