在Postgresql中实现自定义操作符时出现错误。

huangapple go评论56阅读模式
英文:

Error while implementing Custom Operator in Postgresql

问题

在我的Postgres扩展pg_sample_ext中创建一个简单的自定义操作符,但在数据库中实现时出现了错误。下面是更新脚本和错误信息。

更新脚本 pg_sample_ext--1.0.1--1.0.2.sql

-- 创建版本1.0.2所需的对象

-- 自定义操作符:@*
-- 描述:自定义操作符,用于将两个my_type类型的值相乘

-- 创建一个新类型以在操作符中使用。
CREATE TYPE my_type AS (value int);

-- 创建一个SQL函数,定义自定义操作符的行为。
-- 此函数将两个my_type操作数的值相乘。
CREATE FUNCTION multiply_values(left my_type, right my_type) RETURNS my_type AS $$
    SELECT ROW((left.value * right.value))::int;
$$ LANGUAGE SQL IMMUTABLE;

-- 创建一个自定义操作符,用于将两个my_type类型的值相乘。
-- 操作符符号是 @*。
-- 它接受两个my_type类型的操作数,并返回相同类型的值。
-- 行为由SQL函数 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
英文:

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

答案1

得分: 1

主要问题在于rightleft是保留的SQL关键字,所以你不能在没有双引号的情况下使用它们。请避免使用这些标识符。

另外,你的函数必须构造一个新的my_type作为结果:

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';
英文:

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

huangapple
  • 本文由 发表于 2023年7月12日 20:37:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76670633.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定