英文:
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
主要问题在于right
和left
是保留的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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论