Mixing
postgresql: creating a text to integer multiplication operator
I am looking to debug a Microsoft SQL query inside of Posgresql (long story short I prefer the error messages it returns) when I came across some SQL that was multiplying a substring select with an integer, now I could obviously cast each part using ::integer however there are many substrings that need casting and many queries I wanted to run through so I decided that I might make my life easier by using an operator to address this with the use of the following function.
CREATE OR REPLACE FUNCTION public.multiplytext2int(text,integer)
RETURNS integer AS
$$
SELECT CASE
WHEN $1 ~ '^[0-9]+$'
THEN $1::integer * $2
ELSE 0::integer
END
$$
LANGUAGE sql IMMUTABLE;
obviously the logic is not completely airtight but will suffice for the queries I will be running, so then I try to create the operator
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,FUNCTION = multiplytext2int
)
when I run this I get the following error
WARNING: operator attribute "function" not recognized ERROR:
operator procedure must be specified
********** Error **********
ERROR: operator procedure must be specified SQL state: 42P13
what am I doing wrong here?
ultimately I am looking to be able to be able to type SELECT '1'::text * 2::integer without an operator does not exist error.
postgresql postgresql-10
asked Nov 21 '18 at 14:01
LucasLucas
1206
add a comment |
I am looking to debug a Microsoft SQL query inside of Posgresql (long story short I prefer the error messages it returns) when I came across some SQL that was multiplying a substring select with an integer, now I could obviously cast each part using ::integer however there are many substrings that need casting and many queries I wanted to run through so I decided that I might make my life easier by using an operator to address this with the use of the following function.
CREATE OR REPLACE FUNCTION public.multiplytext2int(text,integer)
RETURNS integer AS
$$
SELECT CASE
WHEN $1 ~ '^[0-9]+$'
THEN $1::integer * $2
ELSE 0::integer
END
$$
LANGUAGE sql IMMUTABLE;
obviously the logic is not completely airtight but will suffice for the queries I will be running, so then I try to create the operator
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,FUNCTION = multiplytext2int
)
when I run this I get the following error
WARNING: operator attribute "function" not recognized ERROR:
operator procedure must be specified
********** Error **********
ERROR: operator procedure must be specified SQL state: 42P13
what am I doing wrong here?
ultimately I am looking to be able to be able to type SELECT '1'::text * 2::integer without an operator does not exist error.
postgresql postgresql-10
asked Nov 21 '18 at 14:01
LucasLucas
1206
add a comment |
I am looking to debug a Microsoft SQL query inside of Posgresql (long story short I prefer the error messages it returns) when I came across some SQL that was multiplying a substring select with an integer, now I could obviously cast each part using ::integer however there are many substrings that need casting and many queries I wanted to run through so I decided that I might make my life easier by using an operator to address this with the use of the following function.
CREATE OR REPLACE FUNCTION public.multiplytext2int(text,integer)
RETURNS integer AS
$$
SELECT CASE
WHEN $1 ~ '^[0-9]+$'
THEN $1::integer * $2
ELSE 0::integer
END
$$
LANGUAGE sql IMMUTABLE;
obviously the logic is not completely airtight but will suffice for the queries I will be running, so then I try to create the operator
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,FUNCTION = multiplytext2int
)
when I run this I get the following error
WARNING: operator attribute "function" not recognized ERROR:
operator procedure must be specified
********** Error **********
ERROR: operator procedure must be specified SQL state: 42P13
what am I doing wrong here?
ultimately I am looking to be able to be able to type SELECT '1'::text * 2::integer without an operator does not exist error.
postgresql postgresql-10
asked Nov 21 '18 at 14:01
LucasLucas
1206
I am looking to debug a Microsoft SQL query inside of Posgresql (long story short I prefer the error messages it returns) when I came across some SQL that was multiplying a substring select with an integer, now I could obviously cast each part using ::integer however there are many substrings that need casting and many queries I wanted to run through so I decided that I might make my life easier by using an operator to address this with the use of the following function.
CREATE OR REPLACE FUNCTION public.multiplytext2int(text,integer)
RETURNS integer AS
$$
SELECT CASE
WHEN $1 ~ '^[0-9]+$'
THEN $1::integer * $2
ELSE 0::integer
END
$$
LANGUAGE sql IMMUTABLE;
obviously the logic is not completely airtight but will suffice for the queries I will be running, so then I try to create the operator
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,FUNCTION = multiplytext2int
)
when I run this I get the following error
WARNING: operator attribute "function" not recognized ERROR:
operator procedure must be specified
********** Error **********
ERROR: operator procedure must be specified SQL state: 42P13
what am I doing wrong here?
ultimately I am looking to be able to be able to type SELECT '1'::text * 2::integer without an operator does not exist error.
postgresql postgresql-10
postgresql postgresql-10
asked Nov 21 '18 at 14:01
LucasLucas
1206
asked Nov 21 '18 at 14:01
LucasLucas
1206
edited Nov 21 '18 at 14:14
Lucas
asked Nov 21 '18 at 14:01
LucasLucas
1206
asked Nov 21 '18 at 14:01
LucasLucas
1206
asked Nov 21 '18 at 14:01
LucasLucas
1206
1206
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Although it's a function, the argument to use is procedure.
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,PROCEDURE = multiplytext2int
)
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53413778%2fpostgresql-creating-a-text-to-integer-multiplication-operator%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Although it's a function, the argument to use is procedure.
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,PROCEDURE = multiplytext2int
)
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
add a comment |
Although it's a function, the argument to use is procedure.
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,PROCEDURE = multiplytext2int
)
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
add a comment |
Although it's a function, the argument to use is procedure.
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,PROCEDURE = multiplytext2int
)
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
Although it's a function, the argument to use is procedure.
CREATE OPERATOR * (
LEFTARG = text
,RIGHTARG = integer
,PROCEDURE = multiplytext2int
)
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
edited Nov 21 '18 at 14:20
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
answered Nov 21 '18 at 14:19
Kaushik NayakKaushik Nayak
19.1k41330
19.1k41330
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
add a comment |
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
I just discovered my mistake I was looking through the wrong version of the documentation (postgresql 11) thank you very much for your answer.
– Lucas
Nov 21 '18 at 14:20
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
@Lucas : You're welcome!
– Kaushik Nayak
Nov 21 '18 at 14:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53413778%2fpostgresql-creating-a-text-to-integer-multiplication-operator%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
