Search code examples
postgresqlinliningpostgresql-extensions

Error while implementing Custom Operator in Postgresql


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.

Update script 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
);

ERROR:

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

Solution

  • 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';